Complex update query - 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)

Complex update query

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-10 : 11:59:51

ID Group A | Group B | Group C
1: True | False | True
2: False | False | True
3: True | True | True

TABLE B
This table ideally takes the values from above and squashes it in to one column only adding a value where the column / row has a value of true.
Groups
1: Group A; Group C;
2: Group C;
3: Group A; Group B; Group C;

I hope that made sense. If anyone can point me in the right direction or knows how to do it that would be fantastic.
Thanks for looking.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 13:30:47

[code]
;With CTE (ID,GroupName,Status)
AS
(
SELECT ID
FROM table
UNPIVOT (status FOR groupname IN ([Group A],[Group B ],[Group C]))u
)

SELECT c.ID,
STUFF((SELECT ';' + GroupName FROM CTE WHERE ID = c.ID AND Status='True' ORDER BY GroupName FOR XML PATH('')),1,1,'') AS Groups
FROM (SELECT DISTINCT ID FROM CTE) c
[/code]

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

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-10 : 14:23:03

Amazing reply! Thank you so much You’ve made my day tomorrow a lot better ;-) Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 03:33:44

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources