Coloumns Find and Match - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Coloumns Find and Match

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-03-06 : 13:44:00

Hi Guys i have two table l1 and l2
The table l1 is
C_ID--LEAD_ID--NAME--ADD_FILED
8765----1----adiy----0
1234----2----shot----3
1234----3----jan----3
8765----4----vint----0
1234----5----adity----3
1234----6----abhik----3
1234----7----sorv----3

and the table l2 is
C_ID--LEAD_ID--A_F_NAME--A_F_VALUE
1234----2-------time-------2
1234----2-------date-------3
1234----2-------venue-------4
1234----3-------time-------2
1234----3-------date-------3
1234----3-------venue-------4
1234----5-------time-------2
1234----5-------date-------3
1234----5-------venue-------4
1234----6-------time-------2
1234----6-------date-------3
1234----6-------venue-------4

The expected output wants is
c_id----lead_id----name----time----date----venue
1234----2---------shot-------2------3-------4
1234----3----------jan---- --2------3-------4
1234----5----------adity--- -2------3-------4
1234----6---------abhik------2------3-------4

I should write the c_id in where clause eg (like for this case where c_id='1234') and it should give me expected output

Please help

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-03-06 : 22:08:41

The purpose of add_field column is that it contains 3 rows of c_id and lead_id in table l2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:17:43

assuming the A_F_NAME values are static


SELECT p.c_id,p.lead_id,p.name,q.[time],q.[date],q.venue
FROM l1 p
INNER JOIN (SELECT C_ID,LEAD_ID,
            MAX(CASE WHEN A_F_NAME='time' THEN A_F_VALUE END) AS [time],
            MAX(CASE WHEN A_F_NAME='date' THEN A_F_VALUE END) AS [date],
            MAX(CASE WHEN A_F_NAME='venue' THEN A_F_VALUE END) AS venue
            FROM l2
            GROUP BY C_ID,LEAD_ID
            )q
ON q.C_ID = p.C_ID
AND q.LEAD_ID = p.LEAD_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-03-08 : 01:51:38

Thanks a lot
THE SQL GURU

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:16:02

welcome