July 15, 2011 at 12:52 pm
Hello, I am looking for some help with the following query. I am trying to see if there is a way to return 1 row with all results. Please see code below to better understand what I am trying to accomplish if possible.
Here is my Select
SELECT B.ID, E.SSNO, E.LASTNAME, E.FIRSTNAME, B.STATUS, A.DESCRIPT
FROM EMP E
JOIN BADGE B ON E.ID = B.EMPID
JOIN BADGELINK BL ON B.BADGEKEY = BL.BADGEKEY
JOIN ACCESSLVL A ON BL.ACCLVLID = A.ACCESSLVID
WHERE B.STATUS = 1 AND A.DESCRIPT = 'SwissLog_1' OR A.DESCRIPT = 'SwissLog_2' OR A.DESCRIPT = 'SwissLog_3' OR A.DESCRIPT= 'SwissLog_4'
Which returns this:
ID SSNO FIRSTNAME LATNAME STATUS DESCRIPT
44199 a3354 John Doe 1 SwissLog_1
44199 a3354 John Doe 1 SwissLog_2
44199 a3354 John Doe 1 SwissLog_3
44199 a3354 John Doe 1 SwissLog_4
This is exactly the data I am looking for but if you notice the descript is different for each row, I want to see if I can dynamically create new columns and get it all on the same row. I would like the new result to look something like this.
ID SSNO FIRSTNAME LATNAME STATUS DESCRIPT1 DESCRIPT2 DESCRIPT3 DESCRIPT4
44199 a3354 John Doe 1 SwissLog_1 SwissLog_2 SwissLog_3 SwissLog_4
Then to take this a step further, I want to add some logic to it. IF A.DESCRIPT = ‘SwissLog_1’ THEN DESCRIPT1 = true ELSE DESCRIPT1 = false, IF A.DESCRIPT = ‘SwissLog_2’ TEHN DESCRIPT2 = true ELSE DESCRIPT2 = false, etc.
So the ultimate final result would look something like this:
ID SSNO FIRSTNAME LATNAME STATUS DESCRIPT1 DESCRIPT2 DESCRIPT3 DESCRIPT4
44199 a3354 John Doe 1 true true true true
44200 a1234 Randy Ridgley 1 false true true true
….
Thanks in advance for any help.
July 15, 2011 at 12:58 pm
Randy, search SSC for forums and articles with the keywords PIVOT or "cross tab", to see how you can turn rows into side by side columns. If you are want to create the number of columns dynamically, your code will have to use dynamic sql and first query the database to find out the maximum number of rows in each group, then build your pivot or crosstab accordingly. PIVOT is documented in BOL. The following code is a static example of a cross tab.
declare @sample table(ID int, SSNO varchar(20), FIRSTNAME varchar(20), LASTNAME varchar(20), STATUS_ int, DESCRIPT varchar(50))
insert into @sample
select 44199, 'a3354', 'John', 'Doe', 1, 'SwissLog_1' union all
select 44199, 'a3354', 'John', 'Doe', 1, 'SwissLog_2' union all
select 44199, 'a3354', 'John', 'Doe', 1, 'SwissLog_3' union all
select 44199, 'a3354', 'John', 'Doe', 1, 'SwissLog_4'
;with cte as (select *, ROW_NUMBER() over (PARTITION by ID, SSNO,FIRSTNAME,LASTNAME, STATUS_ order by descript) as rowNo from @sample)
select ID, SSNO,FIRSTNAME,LASTNAME, STATUS_
,MAX(case when rowNo =1 then DESCRIPT else null end) as Descript1
,MAX(case when rowNo =2 then DESCRIPT else null end) as Descript2
,MAX(case when rowNo =3 then DESCRIPT else null end) as Descript3
,MAX(case when rowNo =4 then DESCRIPT else null end) as Descript4
from cte
group by ID, SSNO,FIRSTNAME,LASTNAME, STATUS_
The logic below would be handled by one or more CASE statements:
Then to take this a step further, I want to add some logic to it. IF A.DESCRIPT = ‘SwissLog_1’ THEN DESCRIPT1 = true ELSE DESCRIPT1 = false, IF A.DESCRIPT = ‘SwissLog_2’ TEHN DESCRIPT2 = true ELSE DESCRIPT2 = false, etc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply