Concatenate Results in Row

Question

Concatenate Results in Row

christy-720576

SSC Veteran

Points: 239
More actions
September 15, 2010 at 4:09 am

#224063

Hi,
I have an interesting SQL problem that I'm sure has a simple answer but I keep finding complicated confusing answers so I am going to post here to hope that I can find some clarity.
I have three tables:
Items (List of Concert Items)
Students (List of Students)
IGroups (Project student links)
CREATE TABLE dbo.Items
(
ID Int IDENTITY,
ItemName VARCHAR(12),
ILEngth VARCHAR(32),
PRIMARY KEY (ID)
)
GO
CREATE TABLE dbo.Students
(
ID Int IDENTITY,
SNAme VARCHAR(50),
SCardNO VARCHAR(7),
PRIMARY KEY (ID)
)
GO
CREATE TABLE dbo.IGroups
(
ID Int IDENTITY,
ItemID Int,
StudentID Int,
PRIMARY KEY (ID)
)
GO
SET NOCOUNT ON
INSERT Items (ItemName, ILEngth) VALUES ('Intro', '3.5 mins')
INSERT Items (ItemName, ILEngth) VALUES ('Song 1', '4.8 mins')
INSERT Items (ItemName, ILEngth) VALUES ('Song 2', '1.5 mins')
INSERT Items (ItemName, ILEngth) VALUES ('Scene 1', '9.6 mins')
INSERT Students (Sname, ScardNo) VALUES ('Adam Marks', 'GR2_003')
INSERT Students (Sname, ScardNo) VALUES ('Sarah Wright', 'GR2_006')
INSERT Students (Sname, ScardNo) VALUES ('Mark Frost', 'GR2_012')
INSERT Students (Sname, ScardNo) VALUES ('Tom Samson', 'GR3_056')
INSERT Students (Sname, ScardNo) VALUES ('Paul Da Sosa', 'GR3_078')
INSERT Students (Sname, ScardNo) VALUES ('June Freeman', 'GR3_009')
INSERT Students (Sname, ScardNo) VALUES ('Karen Lindsay', 'GR3_023')
INSERT IGroups (ItemID, StudentID) VALUES (1,1)
INSERT IGroups (ItemID, StudentID) VALUES (2,1)
INSERT IGroups (ItemID, StudentID) VALUES (2,3)
INSERT IGroups (ItemID, StudentID) VALUES (2,5)
INSERT IGroups (ItemID, StudentID) VALUES (3,1)
INSERT IGroups (ItemID, StudentID) VALUES (3,2)
INSERT IGroups (ItemID, StudentID) VALUES (3,3)
INSERT IGroups (ItemID, StudentID) VALUES (3,4)
INSERT IGroups (ItemID, StudentID) VALUES (3,5)
INSERT IGroups (ItemID, StudentID) VALUES (4,6)
INSERT IGroups (ItemID, StudentID) VALUES (4,1)
GO
SELECT Items.ItemName, Items.ILEngth, Students.SCardNO
FROM IGroups LEFT OUTER JOIN
Students ON IGroups.StudentID = Students.ID RIGHT OUTER JOIN
Items ON IGroups.ItemID = Items.ID
GO
DROP table Items
DROP table Students
DROP table Igroups
GO
The select returns 11 rows but what I'd like it to return is a list of the items with the student numbers just listed on the right column.
Intro 3.5 mins GR2_003
Song 1 4.8 mins GR2_003, GR2_012, GR3_078
etc.
What is the best way to get this result? Is there a simple answer?
Regards
Christy

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Christy, first of all, excellent and fantastic way of posting a question, kudos to you. All things put in the right place.. awesome..

Now for the code that will do what you wanted :

; WITH CTE AS (

SELECT Items.ItemName, Items.ILEngth, Students.SCardNO

FROM IGroups

LEFT OUTER JOIN Students

ON IGroups.StudentID = Students.ID

RIGHT OUTER JOIN Items

ON IGroups.ItemID = Items.ID

)

SELECT p1.ItemName , p1.ILEngth,

STUFF ( ( SELECT ','+SCardNO

FROM CTE p2

WHERE p2.ItemName = p1.ItemName

ORDER BY SCardNO

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS SCardNos

FROM CTE p1

GROUP BY p1.ItemName , p1.ILEngth ;

Hope this helps!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

For the explanations of the code, read the following explanations (Thanks Wayne and Skcadvre for your wonderful explanations)

http://www.sqlservercentral.com/Forums/FindPost962505.aspx and http://www.sqlservercentral.com/Forums/FindPost962922.aspx

christy-720576 SSC Veteran Points: 239 More actions · Answer 3

Thanks very much for your quick and comprehensive response!

I've been reading all about recursive queries, loops and hierarchical data structures getting more and more frustrated. All I want is one record with the other data as a list on the right and that is exactly what you've given me. Thanks Again!! 😀

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

First of all, thanks to u too for posting the question fantastically..Secondly, recursive queries, WHILE Loops will all produce the result, but the FOR XML thing is fast and furious.. 🙂

And last but not the least, Thanks for the feedback, Christy!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

I agree... great thread! The only thing that may be wrong in this whole thread is the original requirement. 😉 Why does someone want to denormalize data into a single CSV column, where will it be stored, and how will it be used? This is not a thing that should be stored in a database.

Any chance of getting some additionl information on the actual business need of WHY we might want to do this? Please don't say just "That's the requirement". I'd like to know the real reason behind the requirement. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

christy-720576 SSC Veteran Points: 239 More actions · Answer 6

For my question on the forum I drastically simplified the actual data structures to hopefully get a concise answer.

Basically, I have a screen that needs to list all the Items and most the items details. The user has asked to see which people are involved in those items but if I make it a join then we have multiple lines displayed for each person involved in each item.

This immediately makes it much harder for the user to see the main data that they were originally looking for as there are now up to 5 lines extra per item.

There is absolutely no way that I want to store the concatenated data in the database. I want my database to remain a relational database. I just want it for display on a grid.

If I was going to make a report on this particular data I would just use the original links and do the sorting, grouping, hiding duplicates etc on the report and it would not be any problem at all.

I am actually trying to convince the user that they don't really need to see that data in this particular spot but I had to find out how it could be done if I need to do something similar in the future or they insist on having the info displayed there.

Does this make sense?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Thanks for taking the time to write that up, Christy. It actually does make sense and, to be very honest, I'm quite relieved for you because you came right out and said that you have no intention of storing such data in your DB. It's also good to see someone trying to keep their users out of deep Kimchi instead of just rolling over to get the job done. Well done.

Heh... as a side bar, its nice to see that I'm not the only one with these types of problems in the real world.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sinureddi SSC Journeyman Points: 97 More actions · Answer 8

Hi,

Just out of curiosity....

Why is data concatination, just for the sake of "being displayed in the front end", being achieved at the database level. Is this not tying up your database logic with the display things.

I am trying to refer to the seperation of concerns.

In this case, get the required data from database and write the code (in view, MVC) to display it in whichever way you want it to be displayed. This way you can isolate the database logic from the display logic.

Regards,

Srinivasa Reddy.S

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

sinureddi (9/27/2010)
Hi,
Just out of curiosity....
Why is data concatination, just for the sake of "being displayed in the front end", being achieved at the database level. Is this not tying up your database logic with the display things.
I am trying to refer to the seperation of concerns.
In this case, get the required data from database and write the code (in view, MVC) to display it in whichever way you want it to be displayed. This way you can isolate the database logic from the display logic.
Regards,
Srinivasa Reddy.S

Yes... it does cause the DB Server extra CPU cycles and I agree that this type of thing should probably be done on the front end.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

christy-720576 (10/1/2010)
Ok, so how would you display a list of items but not double up the lines and show a concatenated list of people involved on the front end as optimally as possible?
Please, I would love to learn a better more optimal way!
On the screen I want a grid where the user could pick one or more items to use to generate a report. (as an example)
At the moment I use a Snapshot Dataset to put the data on the grid. Is is only for display so I don't want any editing.
Where would I put the CTE query to make best use of it? Or what other method would work better?

Since I'm a data troll, I'll have to pass on that. My apologies. Maybe sinureddi can give an example.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)