Concat mutiple rows into one row

Question

Post reply

Concat mutiple rows into one row

clint_pow

Say Hey Kid

Points: 690
More actions
November 13, 2008 at 7:48 pm

#383382

Hi,
Can you please assist me regarding my problem, heres the scenario,I want to summarize the list from Van No. rows ,below is the data,
Date : Van No.
1/1/2008 21
1/2/2008 22
1/3/2008 23
1/4/2008 24
1/5/2008 25
1/6/2008 26
How can I query this that I can make this result as :
Date : Van No. Summary Van No.
1/1/2008 21 21,22,23,24,25,26
1/2/2008 22 21,22,23,24,25,26
1/3/2008 23 21,22,23,24,25,26
1/4/2008 24 21,22,23,24,25,26
1/5/2008 25 21,22,23,24,25,26
1/6/2008 26 21,22,23,24,25,26
Thank you in advance.
Regards,
Clint

Viewing 15 posts - 1 through 14 (of 14 total)

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

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

See the following article for how to do the CSV concatenation along with some pitfalls to avoid...

http://www.sqlservercentral.com/articles/Test+Data/61572/

To help us give you a better/tested answer, take a look at the link in my signature.

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

clint_pow Say Hey Kid Points: 690 More actions · Answer 2

hi Jeff,

thank you very much for the link!

Regards,

Clint

clint_pow Say Hey Kid Points: 690 More actions · Answer 3

Hi Jeff,

For clarification, I have read you link, correct me If im wrong your using fnConcatTest to concat the query, Is there any way that we can concat this without the function since I cant create a function in my database cause im using ang SAP program and its prohibited to alter any database that the program involves.

Thank you,

Regards,

Clint

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

Sure... you want all the rows of Summary Van No. to be the same?

Whichever, can you make it easy on me by providing the CREATE TABLE statement and some code to populate it like I mentioned in the "Etiquette" article? 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)

clint_pow Say Hey Kid Points: 690 More actions · Answer 5

Hi Jeff,

Im sorry for that Jeff, Im just want to inform you first thats why im making sure before creating tables for sample. Thank you very much,

Regards,

Clint

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

Got it... thanks, Clint.

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

clint_pow Say Hey Kid Points: 690 More actions · Answer 7

hi jeff,

This is the details,

CREATE TABLE [Query_Concat] (

[DocDate] DATETIME,

[Shipping Line] NVARCHAR(30),

[U_soBLNo] NVARCHAR(20)

)

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Shipping Lines Inc.', N'13')

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Sulpicio Lines Inc.', N'9')

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Shipping Lines Inc.', N'4')

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Shipping Lines Inc.', N'9')

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Shipping Lines Inc.', N'35')

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Shipping Lines Inc.', N'8')

GO

INSERT INTO [Query_Concat] ([DocDate], [Shipping Line], [U_soBLNo])

VALUES ('20080104', N'Shipping Lines Inc.', N'7')

GO

Thank you very much in advance.

Regards,

Clint

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Here's an example. Jeff may have some fine tuning to suggest for this.

[font="Courier New"]

------------- CTE to concatenate U_soBLNo -----------------

;WITH ConcatB(DocDate, [Shipping Line],CB) AS(

SELECT QC.DocDate,[Shipping Line],STUFF((SELECT

', ' + U_soBLNo

FROM

[Query_Concat] QC2

WHERE QC.DocDate = QC2.DocDate AND QC.[Shipping Line] = QC2.[Shipping Line]

FOR XML PATH ('')), 1,2,'')

FROM [Query_Concat] QC

GROUP BY DocDate, [Shipping Line])

-----------------------------------------------------------

------------ SELECT ---------------------------------------

SELECT DISTINCT QC.DocDate, QC.[Shipping Line],CB

FROM [Query_Concat] QC

INNER JOIN ConcatB QC2 ON QC.DocDate = QC2.DocDate AND QC.[Shipping Line] = QC2.[Shipping Line]

-----------------------------------------------------------[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

clint_pow Say Hey Kid Points: 690 More actions · Answer 9

Hi there,

thank you very much for your help, I guess its complicated on my side due to very newbie of me, I have only learn basic SQL but again I relay appreciated it.

Regards,

Clint

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

Clint,

You still haven't answered my question... do you want all the rows of Summary Van No. to be the same?

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

clint_pow Say Hey Kid Points: 690 More actions · Answer 11

Jeff,

Yes Jeff please, sorry I haven't answered the question.

regards,

Clint

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Then, a simple mod to Seth's good code should do...

--===== All of this is a duplicate of the test code you provided

-- with an extra date and a couple of the lines commented out

-- to prove that all U_soBLNo numbers will be returned.

CREATE TABLE [Query_Concat] (

[DocDate] DATETIME,

[Shipping Line] NVARCHAR(30),

[U_soBLNo] NVARCHAR(20)

)

GO