Help to Pivot the Following Table

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 to Pivot the Following Table

Author

Topic

vuyiswamb
Starting Member

6 Posts

Posted - 2009-06-15 : 02:31:52

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.

[CODE]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 [/CODE]

i want to Pivot it and it should look like this

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

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

Do not be tolerent to ignorance but understanding illetaracy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 02:58:43

CYCLETEMPLATE is a column with the value in comma delimited ?
You can use this : concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

vuyiswamb
Starting Member

6 Posts

Posted - 2009-06-15 : 05:08:20

Its a varchar Field, not at the at i want the Field to me Comma delimited based where the Descr is the same.

Thanks

Do not be tolerent to ignorance but understanding illetaracy

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-15 : 05:12:07

try like this as khtan mentioned in above link
SELECT DISTINCT s1.ID,DESCR
STUFF((SELECT DISTINCT ',' + CAST(CYCLETEMPLATE AS VARCHAR(32)) FROM tablename AS s2 WHERE s2.DESCR = s1.DESCR FOR XML PATH('')), 1, 1, '') AS CYCLETEMPLATE
FROM tablename AS s1
ORDER BY s1.ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 06:02:13

[code]DECLARE @Sample TABLE
(
ID INT,
Descr VARCHAR(20),
CycleTemplate INT
)

INSERT @Sample
SELECT 7620, 'Campbell P Dr', 26 UNION ALL
SELECT 7620, 'Campbell P Dr', 27 UNION ALL
SELECT 7620, 'Campbell P Dr', 28 UNION ALL
SELECT 7620, 'Campbell P Dr', 29 UNION ALL
SELECT 7620, 'Campbell P Dr', 31 UNION ALL
SELECT 7621, 'Jones D Dr', 23 UNION ALL
SELECT 7621, 'Jones D Dr', 24 UNION ALL
SELECT 7621, 'Jones D Dr', 29 UNION ALL
SELECT 7621, 'Jones D Dr', 26 UNION ALL
SELECT 7621, 'Jones D Dr', 28 UNION ALL
SELECT 7621, 'Jones D Dr', 33 UNION ALL
SELECT 7621, 'Jones D Dr', 34

SELECT d.ID,
d.Descr,
STUFF(f.g, 1, 1, '') AS CycleTemplate
FROM (
SELECT DISTINCT ID,
Descr
FROM @Sample
) AS d
CROSS APPLY (
SELECT ',' + CAST(s.CycleTemplate AS VARCHAR(MAX))
FROM @Sample AS s
WHERE s.ID = d.ID
GROUP BY s.CycleTemplate
ORDER BY s.CycleTemplate
FOR XML PATH('')
) AS f(g)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources