Help to Pivot the Following Table

[vuyiswam]

Good Morning All

Recently i posted some Sp that i had a problem with while working on a high volume of Data and someone suggested Pivot in sql . I goggled it and found that it was exactly what i was looking for , but i had a problem on the aggregate site because i wanted to Concatenate the Strings. here is the table structure.

ID DESCR CYCLETEMPLATE
====================================
7620 Campbell P Dr 26
7620 Campbell P Dr 27
7620 Campbell P Dr 28
7620 Campbell P Dr 29
7620 Campbell P Dr 31
7621 Jones D Dr 23
7621 Jones D Dr 24
7621 Jones D Dr 26
7621 Jones D Dr 28
7621 Jones D Dr 29
7621 Jones D Dr 33
7621 Jones D Dr 34

i want to Pivot it and it should look like this

ID DESCR CYCLETEMPLATE
===============================
7620 Campbell P Dr 26,2728,29,31
7621 Jones D Dr 23,24,26,28,29,33,34

The CycleTemplate will be Concatinated if the Description is the same and the Discription will not be duplicated again. as you can see in the above example. If your solution duplicated the Cycletemplate its ok, i have an efficient way of removing duplicates in a Field level.


Thank you

[Alsvha]

You don't need to pivot for that, but you can use one of many string concatination tricks.

If you use a newer version of SQL Server (works in 2008, not sure about 2005 as I've only tested it in 2008), you can use the FOR XML PATH syntax for easy doing:


;WITH CTE AS (
SELECT T2.ID, T2.DESCR,
(SELECT CYCLETEMPLATE + ','
FROM YourTable T1
WHERE T1.ID = T2.ID
ORDER BY DESCR
FOR XML PATH('') ) AS CYCLETEMPLATE
FROM YourTable T2
GROUP BY ID, DESCR
)
SELECT ID, DESCR, left(CYCLETEMPLATE, len(CYCLETEMPLATE) -1) AS CYCLETEMPLATE
FROM CTE


Provides the output

7620 Campbell P Dr 26,27,28,29,31
7621 Jones D Dr 23,24,26,28,29,33,34


edit: tested it in SQL Server 2005 this morning and it should work there as well.

[vuyiswam]

Good Day Alsvha

Thanks for your reply

i tried to run it like this


;WITH CTE AS (
SELECT T2.ID, T2.DESCR,
(SELECT CYCLETEMPLATE + ','
FROM FINAL T1
WHERE T1.ID = T2.ID
ORDER BY DESCR
FOR XML PATH('') ) AS CYCLETEMPLATE
FROM FINAL T2
GROUP BY ID, DESCR
)
SELECT ID, DESCR, left(CYCLETEMPLATE, len(CYCLETEMPLATE) -1) AS CYCLETEMPLATE
FROM CTE


in SQL 2005 and it gave me the Following Error


Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ',' to data type int.


The name of my table is "Final"

Thanks

[Alsvha]

That sounds like because your "CYCLETEMPLATE" are integers, so you need to cast them to be able to concatenate them with the string ','
When you have
CYCLETEMPLATE + ','
it tries to numerical add the integer value of ',' to the value of cycletemplate, but seeing as ',' isn't an integer value, it fails. Converting your cycletemplate to a string will solve it.
Otherwise you get the error message as you write.

Something like:




;WITH CTE AS (
SELECT T2.ID, T2.DESCR,
(SELECT RTRIM(LTRIM(STR(CYCLETEMPLATE))) + ','
FROM FINAL T1
WHERE T1.ID = T2.ID
ORDER BY DESCR
FOR XML PATH('') ) AS CYCLETEMPLATE
FROM FINAL T2
GROUP BY ID, DESCR
)
SELECT ID, DESCR, left(CYCLETEMPLATE, len(CYCLETEMPLATE) -1) AS CYCLETEMPLATE
FROM CTE

[vuyiswam]

Now i get the Error


Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.



Thanks

[Alsvha]

Well - that I can't help with in this context, but well - the message indicates you're using a substring somewhere with the wrong parameters?

[ComITSolutions]

your similar kind of question is already answered in
http://www.codeguru.com/forum/showthread.php?t=476370