SQL Query help! - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query help!

Author

Topic

rridgley
Starting Member

1 Post

Posted - 2011-07-15 : 10:13:48

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
….

livezone
Starting Member

10 Posts

Posted - 2011-07-18 : 12:49:41

Answer to your first question is below:

1. Creata a function as shown below

Create Function dbo.FormatData ( @accesslvid int)
returns varchar(1000)
as
Begin

DECLARE @allDescription varchar(1000)
SELECT @allDescription = COALESCE(@allDescription + ' ', '') + DESCRIPT
From ACCESSLVL
Where ACCESSLVID = @accesslvid
return @allDescription
End

change your statement as shown below

SELECT distinct B.ID, E.SSNO, E.LASTNAME, E.FIRSTNAME, B.STATUS,
dbo.FormatData (BL.ACCLVLID ) as 'DESCRIPT1 DESCRIPT2 DESCRIPT3 DESCRIPT4'
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'

Answer to 2nd question should follow

livezone
Starting Member

10 Posts

Posted - 2011-07-18 : 13:02:22

;With CTE AS (
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' )
Select ID, SSNO, LASTNAME, FIRSTNAME, STATUS,
Case When [SwissLog_1] = 'SwissLog_1' Then 'True' else 'False' end as DESCRIPT1,
Case When [SwissLog_2] = 'SwissLog_2' Then 'True' else 'False' end as DESCRIPT2 ,
Case When [SwissLog_3] = 'SwissLog_3' Then 'True' else 'False' end as DESCRIPT3,
Case When [SwissLog_4] = 'SwissLog_4' Then 'True' else 'False' end as DESCRIPT4
from CTE
PIVOT
(
Min(DESCRIPT)
For [DESCRIPT] in ( [SwissLog_1], [SwissLog_2], [SwissLog_3], [SwissLog_4]) ) as Pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources