December 15, 2011 at 9:16 am
Hello All,
I've got the following problem. In my database I have a table with four columns: ID, Rennerid, Achternaam, Uitslag. One rennerid can have multiple uitslag-fields and I want to present them for each rennerid in one column (no ID shown) separated by commas:
1 Lemond 1984/3, 1985/2, 1986/1, etc.
in stead of
1 Lemond 1984/3
1 Lemond 1985/2
1 Lemond 1986/1.
My test data are as follows (full Moden standard):
-- check of tijdelijke tabel bestaat
IF OBJECT_ID('TempDB..#tblHorizontalTest') IS NOT NULL
DROP TABLE #tblHorizontalTest
-- maak de tijdelijke tabel
CREATE TABLE #tblHorizontalTest
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Rennerid] [int] NULL,
[Achternaam] [nvarchar](20) NULL,
[Uitslag] [nvarchar](10) NULL,
)
-- zorg dat de identity kolom wordt gevuld
SET IDENTITY_INSERT #tblHorizontalTest ON
-- vul de tabel
INSERT INTO #tblHorizontalTest
(ID, Rennerid, Achternaam, Uitslag)
SELECT '1','1000','Zoetemelk','1970/2' UNION ALL
SELECT '2','1000','Zoetemelk','1971/2' UNION ALL
SELECT '3','1000','Zoetemelk','1972/5' UNION ALL
SELECT '4','1000','Zoetemelk ','1978/2' UNION ALL
SELECT '5','1000','Zoetemelk ','1979/2' UNION ALL
SELECT '6','1000','Zoetemelk ','1980/1' UNION ALL
SELECT '7','1000','Zoetemelk ','1986/24' UNION ALL
SELECT '8','1406','Merckx','1969/1' UNION ALL
SELECT '9','1406','Merckx','1970/1' UNION ALL
SELECT '10','1406','Merckx','1971/1' UNION ALL
SELECT '11','1406','Merckx','1972/1' UNION ALL
SELECT '12','1406','Merckx','1974/1' UNION ALL
SELECT '13','1406','Merckx','1975/2' UNION ALL
SELECT '14','1406','Merckx','1977/6' UNION ALL
SELECT '15','1','Lemond','1984/3' UNION ALL
SELECT '16','1','Lemond','1985/2' UNION ALL
SELECT '17','1','Lemond','1986/1' UNION ALL
SELECT '18','1','Lemond','1989/1' UNION ALL
SELECT '19','1','Lemond','1990/1' UNION ALL
SELECT '20','1','Lemond','1991/7'
-- terugzetten PK
SET IDENTITY_INSERT #tblHorizontalTest OFF
-- laat de inhoud zien
SELECT * FROM #tblHorizontalTest
According to the horizontal display it should generate 3 rows instead of 20 because there are three different names/rennerid's.
I have tried something with REPLACE as shown in the article of Carl Anderson dd 2009/08/25 (http://www.sqlservercentral.com/articles/T-SQL/67973/), but dit not get it running.
Perhaps anyone has any ideas?
Thanks in advance,
Robert
December 15, 2011 at 9:19 am
Here's a sample, you need to play with this to really understand how this works.
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
December 15, 2011 at 9:42 am
Thanks Ninja, w'll give it a try. I let you know if it works.
December 16, 2011 at 2:28 am
Hello Ninja,
I've played around a bit with the code and it works great!
The problem is solved with your query as follows:
Use WielerDatabaseSQL
Go
Select
T.Achternaam,
Stuff((
Select ',' + C.Uitslag
From vwAATestHorizontaal C
Where C.Rennerid = T.Rennerid
Order By C.Uitslag
For
XML PATH('')) , 1 , 1 , '') As Resultaten
From vwAATestHorizontaal T
Group By T.Achternaam, T.Rennerid
Order By T.Achternaam
The output looks like this:
Lemond1984/3,1985/2,1986/1,1989/1,1990/1,1991/7
Merckx1969/1,1970/1,1971/1,1972/1,1974/1,1975/2,1977/6
Zoetemelk1970/2,1971/2,1972/5,1973/4,1975/4,1976/2,1977/8,1978/2,1979/2,1980/1,1981/4,1982/2,1983/23,1984/30,1985/12,1986/24
Amazing what you can do with so little code: was it on the shelf of have you yourself encountered this problem lately? Just curious.
Thanks for your advice!! :-):-)
Great Christmas present.
Grz,
Robert
December 16, 2011 at 5:06 am
It's a common question. If you don't know about it then you can't find it :-P!
The "correct" google keywords would be something like "Sql Server concatenation stuff for xml".
:hehe:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply