Need help in this 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)

Need help in this query

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-17 : 03:08:38

Hi, i have data in my table as below


SID RED BLUE GREEN HIGH_COLOR
1   12  15   5      NULL
2   45  56   6      NULL
3   67  5    7      NULL
4   78  7    79     NULL

I am looking for output as below


SID RED BLUE GREEN HIGH_COLOR
1   12  15   5      BLUE,RED,GREEN
2   45  56   6      BLUE,RED,GREEN 
3   67  5    7      RED,GREEN,BLUE
4   78  7    79     GREEN,RED,BLUE

I have written query to do this. But i am getting same output for all rows. i am getting result as below


SID RED BLUE GREEN HIGH_COLOR
1   12  15   5      BLUE,RED,GREEN
2   45  56   6      BLUE,RED,GREEN 
3   67  5    7      BLUE,RED,GREEN
4   78  7    79     BLUE,RED,GREEN

My Query is as below.


While EXISTS (Select Top 1 * from Colors Where High_Color is null)
Begin
	Update Colors Set High_Color =(SELECT theCol +  ','  FROM (SELECT top 1 * FROM colors Where High_Color is null) AS t1
	UNPIVOT (theValue FOR 
	theCol IN (RED,BLUE,GREEN)) AS u1 ORDER BY theValue DESC FOR XML PATH(''))
End

My Table Contains around 300 million rows. Pls tell me whether the way i am going to update the table is right? or any other query which doesn't performance.(Its a one time process).
And one more thing..with my query i am getting output appending with comma.But i dont want comma appended at last.(Remove bold red color comma in result)
Ex: BLUE,RED,GREEN,

Regards
MNG

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-17 : 03:37:58

try this
UPDATE t
SET high_color = STUFF((SELECT ','+ thecol FROM (SELECT * FROM @t
UNPIVOT (theValue FOR
theCol IN (RED,BLUE,GREEN)) AS u1 )s
WHERE s.sid = t.sid
ORDER BY sid,theValue DESC FOR XML PATH('') ),1,1,'')
FROM @T t

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-17 : 03:47:33

Thank you Blkr :)

quote:
Originally posted by bklr

try this
UPDATE t
SET high_color = STUFF((SELECT ','+ thecol FROM (SELECT * FROM @t
UNPIVOT (theValue FOR
theCol IN (RED,BLUE,GREEN)) AS u1 )s
WHERE s.sid = t.sid
ORDER BY sid,theValue DESC FOR XML PATH('') ),1,1,'')
FROM @T t

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-17 : 04:05:20

quote:
Originally posted by Mng

Thank you Blkr :)

quote:
Originally posted by bklr

try this
UPDATE t
SET high_color = STUFF((SELECT ','+ thecol FROM (SELECT * FROM @t
UNPIVOT (theValue FOR
theCol IN (RED,BLUE,GREEN)) AS u1 )s
WHERE s.sid = t.sid
ORDER BY sid,theValue DESC FOR XML PATH('') ),1,1,'')
FROM @T t

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 04:52:29

Now compare your method and bklr's method and post the execution time result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources