tsql 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 2012 Forums

Transact-SQL (2012)

tsql query help

Author

Topic

redhills
Starting Member

2 Posts

Posted - 2015-05-04 : 14:28:19

Hello:

I have a table:

ID, Name, Code1, Code2, Code3, Code4, Code5
1, ABC, NULL, 1, 0, 1, NULL
2, XYZ, 1, 1, 1, 1, 1
3, UVW, 0, 0, 1, 1, 1
4, PQR, 1, 1, NULL, 1, NULL

I am trying to write a query that selects the first 3 Code column names from the available 5 which have value = 1

Result would be:

ID, Name, SelectedCode1, SelectedCode2, SelectedCode3
1, ABC, Code2, Code4, NULL
2, XYZ, Code1, Code2, Code3
3, UVW, Code3, Code4, Code5
4, PQR, Code1, Code2, Code4

Thank you!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-04 : 15:01:07

[code]SELECT * FROM
(
SELECT
ID,
[Name],
colname,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY colname) RN
FROM YourTable
UNPIVOT( val FOR colname IN ([Code1],[Code2],[Code3],[Code4],[Code5]))U
WHERE val = 1
)s
PIVOT (MAX(colname) FOR RN IN ([1],[2],[3]) )P;[/code]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-05-04 : 16:57:43

[code]
SELECT Id, name,
'Code' + NULLIF(SUBSTRING(code_values, 1, 1), '') AS SelectedCode1,
'Code' + NULLIF(SUBSTRING(code_values, 2, 1), '') AS SelectedCode2,
'Code' + NULLIF(SUBSTRING(code_values, 3, 1), '') AS SelectedCode3
FROM #test
CROSS APPLY (
SELECT CASE WHEN code1 > 0 THEN '1' ELSE '' END +
CASE WHEN code2 > 0 THEN '2' ELSE '' END +
CASE WHEN code3 > 0 THEN '3' ELSE '' END +
CASE WHEN code4 > 0 THEN '4' ELSE '' END +
CASE WHEN code5 > 0 THEN '5' ELSE '' END AS code_values
) AS ca1

[/code]

redhills
Starting Member

2 Posts

Posted - 2015-05-04 : 17:21:02

Thank you both!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources