How can I return multiple rows as a single row in sql server 2008

Question

0.00/5 (No votes)

See more:

i have something like this:

mtm_id	package_id attribute_id	value
10708	148323	    23	         a
10708	148323	    35	         12
10708	148323	    39	         20
10708	148323	    46	         12
10708	148323	    50	          1

What I'd like is to query back:

mtm_id	package_id value1 value2 value3 value4 value5
10708	148323	    a      12      20     12     1

I need something to get all the rows at once..

thanks in advance..

Posted 3-Jan-14 0:23am

Rashid Choudhary

Updated 3-Jan-14 1:08am

Aarti Meswania

v2

Add a Solution

Comments

Sibasisjena 3-Jan-14 6:42am

You have to do it manually :( .
Declare a variable. use while loop to loop through the records.
Concat all the values you want and then select that concatenated value.

Christian Graus 3-Jan-14 16:15pm

Nonsense

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2014-01-03T10:34:00

Solution 2

There's really no way to do this using pivot, unless you know exactly the number of values you're looking for, and can name them. This SQL will work for any number of values, but as a result, it pulls out the values in your list in to one field:

SQL

select distinct mtm_id, package_id,
stuff((select ', ' + '(' + cast(attribute_id as varchar(10)) + ':' + value + ')' from Rashid r1 where r.mtm_id = r1.mtm_id and r.package_id = r1.package_id
 FOR XML PATH('')),1,2,'') as items
from rashid r

If you just need the attributes, do this:

SQL

select mtm_id, package_id,
stuff((select ', ' + value from Rashid r1 where r.mtm_id = r1.mtm_id and r.package_id = r1.package_id
 FOR XML PATH('')),1,2,'') as items
from rashid r
group by mtm_id, package_id

Note also that the second time I used 'group by' to get rid of the awful use of 'DISTINCT' in the first example. This change can be made to the first example, if you wanted.

Posted 3-Jan-14 10:34am

Christian Graus

Comments

Maciej Los 3-Jan-14 17:05pm

Holy true!

Christian Graus 3-Jan-14 17:11pm

*grin* I hope to do an article on this sort of thing, soon. PIVOT is astonishingly limited in what it can be used for. Great for days of week. Terrible for arbitrary lists.

Maciej Los 3-Jan-14 17:22pm

I'll read it with pleasure ;)
What you think about ROW_NUMBER() OVER(PARTITION BY [mtm_id] ORDER BY [package_id]) with SELECT statement as a source of pivot? It would be works... ;)

Christian Graus 3-Jan-14 17:39pm

Can you give me the full SQL ? Your row_number code gives a different number for each row, grouped by mtm_id. It would provide a sequence to group by, so long as there's never the same mtm_id and different package_id ( I created extra rows to deal with both possibilities in my test code ).

Maciej Los 3-Jan-14 17:42pm

Of course ;)
Have a look at my answer (solution 3)

CPallini 3-Jan-14 19:04pm

5.

Christian Graus 3-Jan-14 19:08pm

*blush*

Maciej Los · Accepted Answer · 2014-01-03T11:40:00

Solution 3

Another way is to use ROW_NUMBER()[^] function:

Test it:

SQL

CREATE TABLE #test (mtm_id INT, package_id INT,  attribute_id INT, value VARCHAR(30))

INSERT INTO #test (mtm_id, package_id, attribute_id, value)
VALUES(10708, 148323, 23, 'a'),
(10708, 148323, 35, '12'),
(10708, 148323, 39, '20'),
(10708, 148323, 46, '12'),
(10708, 148323, 50, '1')

SELECT mtm_id, package_id,  [1], [2], [3], [4], [5], [6], [7]
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id,  [value]
    FROM #test
    ) AS DT
PIVOT (MAX([value]) FOR RowNo IN([1],[2],[3],[4],[5],[6],[7])) AS PT

DROP TABLE #test

Note: in above example number of columns were added manually. It would be created 'on the fly' by using STUFF[^] function ;)
Example: pivot multiple columns into rows in sql server[^]

Result:

mtm_id  p.._id  [1]     [2]     [3]     [4]     [5]     [6]     [7]
10708	148323	a	12	20	12	1	NULL	NULL

[EDIT]

dynamic version ;)

SQL

CREATE TABLE #test (mtm_id INT, package_id INT,  attribute_id INT, value VARCHAR(30))

INSERT INTO #test (mtm_id, package_id, attribute_id, value)
VALUES(10708, 148323, 23, 'a'),
(10708, 148323, 35, '12'),
(10708, 148323, 39, '20'),
(10708, 148323, 46, '12'),
(10708, 148323, 50, '1'),
(10709, 148323, 39, 'b'),
(10710, 148323, 46, 'c'),
(10710, 148323, 46, '1'),
(10710, 148323, 46, '3'),
(10710, 148323, 46, '5'),
(10710, 148323, 46, '7'),
(10710, 148323, 46, '9'),
(10710, 148323, 46, '11')

DECLARE @cols VARCHAR(300) = ''
DECLARE @dt VARCHAR(2000) = ''
DECLARE @pt VARCHAR(MAX) = ''

SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(VARCHAR(10), C.RowNo)
            FROM (
			    SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id,  [value] 
			    FROM #test
				) C
            FOR XML PATH('')),1,2,'') + ']'
--SET @cols += ']'

--SELECT @cols AS Cols

SET @dt = 'SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id,  [value] ' +
	'FROM #test'
--EXEC(@dt)

SET @pt = 'SELECT mtm_id, package_id, ' + @cols +
' FROM (' + @dt + ') AS DT ' +
' PIVOT (MAX([value]) FOR RowNo IN(' + @cols + ')) AS PT '

--PRINT @pt
EXEC(@pt)

DROP TABLE #test

[/EDIT]

Posted 3-Jan-14 11:40am

Maciej Los

Updated 3-Jan-14 12:33pm

v3

Comments

Christian Graus 3-Jan-14 17:48pm

Yeah, this is how to do it, with pivot. The benefit is that you get one value per cell. The down side is, you need to define the maximum number of possible values, and if you define too many, you get a lot of nulls. In fact, I got this from my data:

mtm_id package_id 1 2 3 4 5 6 7
10708 148323 a 12 20 12 1 NULL NULL
10710 148323 1 NULL NULL NULL NULL NULL NULL
10708 148326 NULL NULL NULL NULL NULL 1 NULL

As you can see, I defined two rows with the same MTM_ID. Let me try something....

Yeah, I inserted more rows, and got this:

mtm_id package_id 1 2 3 4 5 6 7
10708 148323 a 12 20 12 1 NULL NULL
10710 148323 1 NULL NULL NULL NULL NULL NULL
10708 148326 NULL NULL NULL NULL NULL 1 4

Note, there's four values for that last row, but because of how the row number works ( like I said before ), the sequence continues across two values with the same mtm_id but a different package_id.

SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id, [value]
FROM Rashid

for me gives

1 10708 148323 a
2 10708 148323 12
3 10708 148323 20
4 10708 148323 12
5 10708 148323 1
6 10708 148326 1
7 10708 148326 4
8 10708 148326 as
9 10708 148326 ahd
1 10710 148323 1

So, this solution will work for some data, but not for all possible combinations of data.

Maciej Los 3-Jan-14 17:54pm

I know what you mean. And there is quite possible to get number of NULL's if count of [values] for each mtm_id would be differ.
But, you need to agree with me that it is possible to use pivot with some extra conditions ;)

Christian Graus 3-Jan-14 17:58pm

Yes. I never said pivot was impossible. It's just ugly. The pivot does not support some data ( same mtm_id, different package_id ), and even if it could, you still need to define the max number of possible rows, or some will just get lost, silently. Of course, you do get one value per cell. So it really depends on the business need.

Maciej Los 3-Jan-14 18:03pm

There's really no way to do this using pivot, unless you know exactly the number of values you're looking for, and can name them.
;)

Christian Graus 3-Jan-14 18:09pm

Yes, exactly. You have to know the maximum number of possible values, and give them a name. You've done both, using row_number, and a potential maximum of 7.

Maciej Los 3-Jan-14 18:22pm

Christian, as i mentioned, it's possible to create list of columns 'on the fly'. ;)

Christian Graus 3-Jan-14 18:33pm

But your pivot says this: FOR RowNo IN([1],[2],[3],[4],[5],[6],[7])). you need to maintain this list, in two places, as the number of fields grows. What you've done is exactly how I expected the pivot to look, and it's a correct answer, and perhaps the right one in some circumstances. I don't think my answer is *better*, it's just a different approach, suited to different business needs and priorities.

Maciej Los 3-Jan-14 18:41pm

I agree with you, Christian in 100%. I'm sure, your answer is better than mine, but i wanted to show an alternative ;)

Maciej Los 3-Jan-14 18:42pm

BTW: see updated answer ;)

Christian Graus 3-Jan-14 18:43pm

Yeah, I knew you can do that, too, but there's no point creating a proc that just calls EXEC, you may as well string mash SQL in code. I think this question has been given all possible answers now, which is great for the OP, and it's always interesting to discuss and see how other people do things.

Maciej Los 3-Jan-14 18:50pm

there's no point creating a proc that just calls EXEC - i need to disagree :(
Let's finish this discussion at this point, OK?

Christian Graus 3-Jan-14 19:08pm

OK, we can stop there. But, I'll NEVER do EXEC in a proc.

Maciej Los 3-Jan-14 19:13pm

That's better ;)
Sorry, it was a joke ;)
It served as a pretext for further discussion.
Now we can't stop, really ;)

Cheers, Christian!

Christian Graus 3-Jan-14 20:59pm

*grin*

CPallini 3-Jan-14 19:04pm

5.

Maciej Los 3-Jan-14 19:07pm

Thank you, Carlo ;)

CPallini · Accepted Answer · 2014-01-03T00:40:00

Solution 1

Have a look at PIVOT[^] function.

Posted 3-Jan-14 0:40am

CPallini

Comments

Orcun Iyigun 3-Jan-14 6:53am

Right +5ed.

CPallini 3-Jan-14 7:02am

Thank you.

thatraja 3-Jan-14 9:35am

5!

CPallini 3-Jan-14 9:37am

Thank you.

Maciej Los 3-Jan-14 17:46pm

Almost perfect, Carlo ;)
The reason of 4: pivot isn't possible without extra calculations.
Please, see my answer (solution 3) to find out why ;)

CPallini 3-Jan-14 19:04pm

Wow, I see the SQL wizards in action.

Maciej Los 3-Jan-14 19:06pm

;)