Help Me - 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)

Help Me

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-25 : 06:16:14

Hello,
This is my Table
SP_ID SP_Name ColumnName
----- ------------------------ ----------------
1 sp_GenerateDistanceOverview Total Distance
1 sp_GenerateDistanceOverview Standing Distance
1 sp_GenerateDistanceOverview Standing Time
3 sp_GenerateEventsOverview Passes
5 sp_GenerateDefensiveOverview Interceptions

My output should be like below
Sp_ID SP_NAME Column Name
----------------------------------------------------------------------------------------
1 sp_GenerateDistanceOverview ,[Total Distance ],[Standing Distance],[Standing Time]
3 sp_GenerateEventsOverview ,[Passes]
5 sp_GenerateDefensiveOverview ,[Interceptions]
-----------------------------------------------------------------------------------------

How to combine all the columns into a single variable and display as above

Thanks in advance

Solutions are easy. Understanding the problem, now, that's the hard part

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 06:20:38

[code]SELECT t.SP_ID,t.SP_Name,
LEFT(cl.ColList,LEN(cl.ColList)-1) AS ColumnName
FROM (SELECT DISTINCT SP_ID,SP_Name FROM YourTable) t
CROSS APPLY (SELECT ColumnName + ',' AS [text()]
FROM YourTable
WHERE SP_ID=t.SP_ID
FOR XML PATH(''))cl(ColList)[/code]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-25 : 06:26:11

Thanks you so much, Its amazing query. I am really very happy with this query

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-25 : 06:30:31

Hello Visakh,

I was not able to print ']' at the end of each row, can i know why this happens
1 sp_GenerateDistanceOverview ,[Total Distance],[Standing Distance],[Standing Time],[Num Standing],[Walking Distance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 06:35:37

quote:
Originally posted by ganeshkumar08

Hello Visakh,

I was not able to print ']' at the end of each row, can i know why this happens
1 sp_GenerateDistanceOverview ,[Total Distance],[Standing Distance],[Standing Time],[Num Standing],[Walking Distance

modify like this to get that
SELECT t.SP_ID,t.SP_Name,
LEFT(cl.ColList,LEN(cl.ColList)-1) AS ColumnName
FROM (SELECT DISTINCT SP_ID,SP_Name FROM YourTable) t
CROSS APPLY (SELECT '[' + ColumnName + '],' AS [text()]
FROM YourTable
WHERE SP_ID=t.SP_ID
FOR XML PATH(''))cl(ColList)

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-25 : 06:42:13

yes i done the same, but still i didnt get. Finally i modified like this

SELECT t.SP_ID,t.SP_Name,
cl.ColList AS ColumnName
FROM (SELECT DISTINCT SP_ID,SP_Name FROM YourTable) t
CROSS APPLY (SELECT '[' + ColumnName + '],' AS [text()]
FROM YourTable
WHERE SP_ID=t.SP_ID
FOR XML PATH(''))cl(ColList)

Now i got exact result by modifying like above.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources