Display Data Horizontal - SQL Server Forums

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

Display Data Horizontal

Author

Topic

LauraFields
Starting Member

4 Posts

Posted - 2011-06-22 : 10:16:30

I have a simple sql statement which I'm trying to put into a report.

Select customer.name, customer_group.prod_id, groups.group_id

From customer_group cg
JOIN customer c ON c.name = cg.name
JOIN groups g ON g.group_id = cg.group_id

Where cg.name = :name
AND c.name IS NOT NULL

Order By name, group_id

This is what the data displays

Name prod_id group_id
Dave 100 50
Dave 98 50
Dave 86 100
Dave 101 100

I want it to be like this

Name prod_id group_id
Dave 100, 98 50
Dave 86, 101 100

It groups by group_id and displays the data horizontally.

I've tried using several options but can't get anything to work, I keep getting errors.
Any help would be appreciated.

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-06-22 : 12:31:24

OK I'm going to give you half the answer - because I'm going home in five minutes.

What you're trying to do is to concatenate table results into a single string.

In order to try to test this I created a flat table of your results - you will obviously have to tailor this to suit your own database set-up. I did this here:

SELECT 'Dave' as Name, 100 as Prod_ID, 50 as GroupID 
INTO #customer
UNION ALL
SELECT 'Dave',98,50 UNION ALL
SELECT 'Dave',86,100 UNION ALL
SELECT 'Dave',101,100

Now what you need to do is to concatenate all the Prod_IDs for each Group_ID. This code below can be converted into a function to do this:

DECLARE @GroupID INT
SET @GroupID = 100 -- this is just to test the query

DECLARE @strOutput VARCHAR(100) -- this will be your output
SET @strOutput = '' -- this is set to '' because you'll be appending values to it

SELECT @strOutput = @strOutput + PID + ', '  
-- this appends each value onto the end of the variable
	FROM (SELECT CONVERT(VARCHAR,Prod_ID) as PID FROM #customer WHERE GroupID= @GroupID) x

SELECT @strOutput

When you pass in the Group_ID of 100, it will return '86,101'.

Well it doesn't actually, it returns '86,101,' so you'll have to remove the trailing comma. You'll also need to account for when nothing is returned as that might cause it to fail.

Sorry I can't be more helpful but hopefully this will provide the basis for you to work the rest out for yourself.. . or someone else to jump in and finish it off.

Good luck.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources