Like to have data returned in 1 row vs. multiple rows

Question

Post reply

Like to have data returned in 1 row vs. multiple rows

denis.gendera

Right there with Babe

Points: 733
More actions
June 9, 2015 at 1:11 pm

#318764

Got following data
create table #test_table
(
column1 varchar(2),
column2 varchar(30)
)
insert into #test_table (column1, column2) values ('A', 'Belgium')
insert into #test_table (column1, column2) values ('A', 'France')
insert into #test_table (column1, column2) values ('B', 'Germany')
insert into #test_table (column1, column2) values ('B', 'Italy')
select * from #test_table
The output is following
column1column2
A Belgium
A France
B Germany
B Italy
I would like to have output returned following way
column1column2
A Belgium | France
B Germany | Italy
any suggestions. I was looking at Pivot but does not seem to work.
thx.

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

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

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

Is the number of columns in the final output a given or do we have to have the code figure that out?

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

denis.gendera Right there with Babe Points: 733 More actions · Answer 2

It's only 1 column, I would like to concatenate the output from the various columns, using "|" as separator.

Main idea is to have everything returned in 1 row.

David Burrows SSC Guru Points: 65143 More actions · Answer 3

Jeff Moden (6/9/2015)
Is the number of columns in the final output a given or do we have to have the code figure that out?

Shouldn't that be max number of rows per column1?

The use of 'FOR XML' may be the answer, there are several articles on this site will show you how, I think even Jeff may have published one:-D

*Edited*

And here it is http://www.sqlservercentral.com/articles/Test+Data/61572/

Far away is close at hand in the images of elsewhere.
Anon.

denis.gendera Right there with Babe Points: 733 More actions · Answer 4

I did find solution to my problem, using the for xml.

Query

SELECT

column1,

(SELECT COLUMN2 + '; ' FROM #test_table B WHERE B.column1 = A.column1

FOR XML PATH('')) [COLUMN]

FROM #test_table A

GROUP BY A.column1

ORDER BY 1

Output

column1COLUMN

A Belgium; France;

B Germany; Italy;

Thx for pointing me in the right direction.

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Your welcome

For quick, excellent and efficient answers Jeff's articles should always be your starting point 😉

Far away is close at hand in the images of elsewhere.
Anon.

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

David Burrows (6/10/2015)
Jeff Moden (6/9/2015)
Is the number of columns in the final output a given or do we have to have the code figure that out?
Shouldn't that be max number of rows per column1?
The use of 'FOR XML' may be the answer, there are several articles on this site will show you how, I think even Jeff may have published one:-D
*Edited*
And here it is http://www.sqlservercentral.com/articles/Test+Data/61572/

I was thinking that he actually wanted a CROSSTAB/PIVOT rather than a concatenation. 😀

Wow! That's an old article. I forgot I had that in there.

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

David Burrows SSC Guru Points: 65143 More actions · Answer 7

Jeff Moden (6/10/2015)
David Burrows (6/10/2015)
Jeff Moden (6/9/2015)
Is the number of columns in the final output a given or do we have to have the code figure that out?
Shouldn't that be max number of rows per column1?
The use of 'FOR XML' may be the answer, there are several articles on this site will show you how, I think even Jeff may have published one:-D
*Edited*
And here it is http://www.sqlservercentral.com/articles/Test+Data/61572/
I was thinking that he actually wanted a CROSSTAB/PIVOT rather than a concatenation. 😀
Wow! That's an old article. I forgot I had that in there.

Well you are a gold mine of information, you just have to dig deep enough and brush the cobwebs away 😛 :w00t:

Far away is close at hand in the images of elsewhere.
Anon.