How to combine multiple rows of data on same line.

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

General SQL Server Forums

New to SQL Server Programming

How to combine multiple rows of data on same line.

Author

Topic

bob3s
Starting Member

3 Posts

Posted - 2010-04-14 : 15:53:34

I have got the following problem:

I need to write a query that returns musicartists with their genres. Each artist should only appear once with the genres merged into one row --> for example:

INSTEAD OF

ArtistID ArtistName Description Price Type Genre
----------------------------------------------------------------
1 Bloc Party This is a band €2000 Band Indie
1 Bloc Party This is a band €2000 Band Rock

I WANT TO GET:

ArtistID ArtistName Description Price Type Genre
----------------------------------------------------------------
1 Bloc Party This is a band €2000 Band Indie, Rock

I've found numerous blogs and forums on the internet, but I'm still having issues with my code. This is my current sql code:

CREATE FUNCTION [dbo].[GenreOnSameLine2](@GenreID int, @Genre nvarchar(255))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @OnSameLine varchar(max)
set @OnSameLine=''
SELECT @OnSameLine = @OnSameLine + coalesce(Music.Genre.Genre,'')+@Genre
FROM (Music.Artist INNER JOIN Music.ArtistTypeGenre ON Music.Artist.ArtistID = Music.ArtistTypeGenre.ArtistID) INNER JOIN Music.Genre ON Music.Genre.GenreID = Music.ArtistTypeGenre.GenreID
AND Music.Artist.ArtistID = @GenreID
set @OnSameLine = substring(@OnSameLine,1,len(@OnSameLine)-1)
RETURN @OnSameLine
END;

SELECT DISTINCT dbo.GenreOnSameLine2(Music.Genre.GenreID,', ') AS GenreList, af.Image, a.ArtistID, a.ArtistName, a.Description, t.Type FROM Artist a, ArtistTypeGenre atg, Type t, Genre g, Image af, ArtistImage aa WHERE a.ArtistID = atg.ArtistID AND atg.GenreID = g.GenreId AND atg.TypeID = t.TypeID AND a.ArtistID = aa.ArtistID AND aa.ImageID = af.ID AND Upper(a.ArtistName) = 'BOB DRIES'

My table structure is as follows:

Artist
-------
ArtistID (PK)
ArtistName
Description
Website
Price
Biography
CV
ContactID

ArtistTypeGenre
----------------
ArtistID (PK)
GenreID (PK)
TypeID (PK)

Genre
-------
GenreID (PK)
Genre

Type
-----
TypeID (PK)
Type

Relations are:
--------------
Artist.ArtistID = ArtistTypeGenre.ArtistID
ArtistTypeGenre.GenreID = Genre.GenreID
ArtistTypeGenre.TypeID = Type.TypeID

Can anyone help me with my problem? I've never tried somthing as complicated as this so I'm quite a noob :p.

Many thanx in advance,

Bob3s

bob3s
Starting Member

3 Posts

Posted - 2010-04-14 : 18:55:10

Noone?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 19:54:52

Here's a simplified sample that should point you in the right direction:


CREATE TABLE #tmp (
	ArtistID INT,
	ArtistName VARCHAR(30),
	Description VARCHAR(30), 
	Price Money,
	Type VARCHAR(30),
	Genre VARCHAR(30)
)

INSERT INTO #tmp
SELECT 1, 'Bloc Party', 'This is a band', 2000, 'Band', 'Indie'
UNION ALL SELECT 1, 'Bloc Party', 'This is a band', 2000, 'Band', 'Rock'
GO

SELECT DISTINCT a.ArtistID,
	a.ArtistName,
	a.Description, 
	a.Price,
	a.Type,
	g.Gen
FROM #tmp a
CROSS APPLY ( SELECT STUFF((
		SELECT ', ' + Genre 
		FROM #tmp 
		WHERE ArtistID = a.ArtistID
		FOR XML PATH('') ), 1, 2, '') AS Gen) g
GO

DROP TABLE #tmp

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 06:55:08

Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 07:08:59

I find the OUTER APPLY / CROSS APPLY method(s) more readable. Something like


SELECT
	a.[ArtistName]
	, LEFT(gen.[genres], LEN(gen.[genres]) - 1) AS [Genres]
FROM
	Artist a
	OUTER APPLY (
		SELECT
			g.Genre + ', '
		FROM
			AtrtistTypeGenre atg
			JOIN genre g ON g.[genreID] = atg.[genreId]
		WHERE
			atg.[artistID] = a.[artistID]
		ORDER BY
			g.[genre] ASC
		FOR XML PATH ('')
		)
		gen ([genres])

Note -- you need 2005 or higher for this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bob3s
Starting Member

3 Posts

Posted - 2010-04-15 : 08:14:55

Thank you so much!

Transact charlies post did it for me! I can't thank you guys enough!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources