September 5, 2019 at 3:00 pm
I have a table : tableA with the following column names:
DateCol1,Col2,1,2,3,4,5,6 so there are 8 columns in totals(1,2,3,4,5,6 are also column names)
1,2,3,4,5,6 will have either a zero '0' as their value or '1' as the value.
Ex: DateCol1 Col2 1 2 3 4 5 6
1/1/2019 A 0 0 1 1 0 0
2/1/2019 B 1 0 1 1 0 1
3/1/2019 C 1 1 1 1 1 0
4/1/2019 D 0 0 1 1 1 0
I need a query which will give me the names of the first 3 columns among 1,2,3,4,5,6 columns where the first three 0's are found.
Row 1 should return 1,2,5,6
Row 2 should return 2 and 5
Row 3 should return only 6
Row 4 should return 1,2, and 6.
Thanks
September 5, 2019 at 3:17 pm
SELECT DateCol1, Col2,
STUFF(CASE WHEN [1] = 0 THEN ',1' ELSE '' END +
CASE WHEN [2] = 0 THEN ',2' ELSE '' END +
CASE WHEN [3] = 0 THEN ',3' ELSE '' END +
CASE WHEN [4] = 0 THEN ',4' ELSE '' END +
CASE WHEN [5] = 0 THEN ',5' ELSE '' END +
CASE WHEN [6] = 0 THEN ',6' ELSE '' END, 1, 1, '') AS Cols_With_0
FROM dbo.table_name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply