Combine multiple rows

Question

Post reply

Combine multiple rows

Bruna

SSC Journeyman

Points: 87
More actions
April 29, 2008 at 7:40 pm

#206818

I have a SELECT:
select client.id,service.id
from cliente,service
where cliente.id= service.id_cliente
RESULT
cliente.id service.id
1 10
1 20
2 5
2 1
2 35
3 6
How can I get this RESULT:
cliente.id service.id1 service.id2 service.id3
1 10 20
2 5 1 35
3 6

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

What is the maximum number of ServiceID's you can have per ClientID?

--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)

Bruna SSC Journeyman Points: 87 More actions · Answer 2

I dont know, Its dynamic...

Could be 3-4-5-6... 🙁

Putts SSCommitted Points: 1857 More actions · Answer 3

Dynamic is gonna cause the problem there......maybe someone else knows a way of doing a SELECT statement with a dynamic number of Select fields but I've never come across one.

Here's an alternative....this function is designed to create a comma delimited list of all last names for each first name from an Employees table:

CREATE FUNCTION dbo.TMP_FX_LASTNAMES(@firstName varchar(100),@lastNameList varchar(600),@count int) RETURNS varchar(600)
BEGIN
DECLARE @newName varchar(100)
IF @count = 32
BEGIN
return 'Maximum number of names reached'
END
ELSE
BEGIN
IF Exists(SELECT RTRIM(LASTNAME) FROM EMPLOYEES WHERE RTRIM(FIRSTNAME) = @firstName AND CHARINDEX(RTRIM(LASTNAME),@lastNameList) = 0)
BEGIN
SELECT TOP 1 @newName = RTRIM(LASTNAME) FROM EMPLOYEES WHERE RTRIM(FIRSTNAME) = @firstName AND CHARINDEX(RTRIM(LASTNAME),@lastNameList) = 0
IF @lastNameList = ''
BEGIN
SET @lastNameList = @newName
END
ELSE
BEGIN
SET @lastNameList = @lastNameList + ', ' + @newName
END
SET @lastNameList = dbo.TMP_FX_LASTNAMES(@firstName,@lastNameList,(@count+1))
END
return @lastNameList
END
return ''
END

I know it's not exactly what you are looking for but if you're eventually going to be using the result of the query within something like ASP or .NET or some other language, there's a chance that you could turn the second column into an array, add one column and put your ID in there and then you have what you need.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Bruna (4/30/2008)
I dont know, Its dynamic...
Could be 3-4-5-6... 🙁

Putts (4/30/2008)
Dynamic is gonna cause the problem there......maybe someone else knows a way of doing a SELECT statement with a dynamic number of Select fields but I've never come across one.

Heh... now you have... 😀

--===== Do a little setup to make the message window pretty

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

--===== Create and populate a demonstration table

DECLARE @DemoTable TABLE

([cliente.id] INT, [service.id] INT)

INSERT INTO @DemoTable

([cliente.id],[service.id])

SELECT 1,10 UNION ALL

SELECT 1,20 UNION ALL

SELECT 2,5 UNION ALL

SELECT 2,1 UNION ALL

SELECT 2,35 UNION ALL

SELECT 3,6

--===== Declare a variables to hold dynamic SQL

DECLARE @SQL1 VARCHAR(MAX)

DECLARE @SQL2 VARCHAR(MAX)

DECLARE @SQL3 VARCHAR(MAX)

DECLARE @MyRankMax INT

--===== Build a working table with raning for each row

SELECT MyRank = ROW_NUMBER() OVER (PARTITION BY [cliente.id]

ORDER BY [cliente.id],[service.id]),

[cliente.id],

[service.id]

INTO #MyWork

FROM @DemoTable

--===== Remember the largest rank to limit the number of columns

SELECT @MyRankMax = MAX(MyRank) FROM #MyWork

--===== Build the dynamic SQL in it's component parts

SELECT @SQL1 = 'SELECT [cliente.id],'+CHAR(10)

SELECT @SQL2 = COALESCE(@SQL2+','+CHAR(10),'')

+ 'MAX(CASE WHEN MyRank = '+LTRIM(STR(N))

+ ' THEN [service.id] ELSE NULL END) AS [Service.ID'

+ LTRIM(STR(N))+']'

FROM dbo.Tally

WHERE N <= @MyRankMax

SELECT @SQL3 = CHAR(10)+'FROM #MyWork GROUP BY [cliente.id] ORDER BY [cliente.id]'

--===== Print what the dynamic SQL looks like (Cross-tab)

-- This can be commented out for production

PRINT @SQL1+@SQL2+@SQL3

--===== Execute the dynamic SQL to get a single return

EXEC (@SQL1+@SQL2+@SQL3)

--===== Housekeeping for reruns of the test

DROP TABLE #MyWork

And, if you don't already have the wonderfully useful utility table known as a "Tally" table, please visit the following URL for how to make one...

http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

--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)

Putts SSCommitted Points: 1857 More actions · Answer 5

Yeah.....I thought about a stored proc to creat a temp table and go that route but I actually liked the concept of having a more structured output.

I'm always looking at this from a Software Developer aspect than I am a Database Developer viewpoint so from that stance I was trying to provide a way that he'd be able always know guaranteed where the data would be. With the function, the data he'd need would always be in the second column which might make it easier to code around.

Not even sure what he's using it for but now he should know a couple ways of doing it 😛

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Yeah... I agree... if it's a return to the GUI, the comma separated list would probably be the way to go for the very reasons you stated. 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)