Concatenate Multiple String Rows into One Row

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

Concatenate Multiple String Rows into One Row

Author

Topic

hipower
Starting Member

2 Posts

Posted - 2011-11-29 : 12:37:07

All,
We have a Pervasive database that stores multiple rows of notes per customer quote number. I would like to change the table from a single column of notes to multiple columns, effectively eliminating the excess rows in the table. This is how the table looks now:

QUOTE_NO QUOTE_LINE NOTES_SEQ TEXT
12345          0010           001            blah blah
12345          0010           002            blah cont.
67891          0010           001            he said
67891          0010           002            she said.

I used the following SQL statement:

CREATE
VIEW "V_QUOTE_NOTE_CON" AS
SELECT
"T1" ."QUOTE_NO" ,
"T1" ."QUOTE_LINE",
CASE WHEN "T1" ."NOTES_SEQ" = '0000' THEN "T1" ."TEXT" END "N1" ,
CASE WHEN "T1" ."NOTES_SEQ" = '0001' THEN "T1" ."TEXT" END "N2" ,
CASE WHEN "T1" ."NOTES_SEQ" = '0002' THEN "T1" ."TEXT" END "N3" ,
CASE WHEN "T1" ."NOTES_SEQ" = '0003' THEN "T1" ."TEXT" END "N4" ,
CASE WHEN "T1" ."NOTES_SEQ" = '0004' THEN "T1" ."TEXT" END "N5"
FROM "V_QUOTE_NOTES" "T1"
GROUP BY
"T1" ."QUOTE_LINE" ,
"T1" ."QUOTE_NO",
"T1" ."NOTES_SEQ" ,
"T1" ."TEXT"

Which resulted in:
QUOTE_NO  QUOTE_LINE       N1       N2       N3       N4        N5
12345           0010           blah blah
12345           0010                       blah cont.
67891           0010           he said
67891           0010                       she said

What I am looking to do would result in:
QUOTE_NO  QUOTE_LINE       N1       N2       N3       N4        N5
12345           0010           blah blah  blah cont.
67891           0010           he said    she said

Any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 12:41:03

make it like


CREATE
VIEW "V_QUOTE_NOTE_CON" AS
SELECT 
"T1" ."QUOTE_NO" ,
"T1" ."QUOTE_LINE",
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0000' THEN "T1" ."TEXT" END) "N1" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0001' THEN "T1" ."TEXT" END) "N2" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0002' THEN "T1" ."TEXT" END) "N3" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0003' THEN "T1" ."TEXT" END) "N4" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0004' THEN "T1" ."TEXT" END) "N5" 
FROM "V_QUOTE_NOTES" "T1"
GROUP BY
"T1" ."QUOTE_LINE" ,
"T1" ."QUOTE_NO"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hipower
Starting Member

2 Posts

Posted - 2011-11-29 : 13:27:54

That was it! Thank you so much for the fast response!

quote:

Originally posted by visakh16

make it like


CREATE
VIEW "V_QUOTE_NOTE_CON" AS
SELECT 
"T1" ."QUOTE_NO" ,
"T1" ."QUOTE_LINE",
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0000' THEN "T1" ."TEXT" END) "N1" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0001' THEN "T1" ."TEXT" END) "N2" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0002' THEN "T1" ."TEXT" END) "N3" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0003' THEN "T1" ."TEXT" END) "N4" ,
MAX(CASE WHEN "T1" ."NOTES_SEQ" = '0004' THEN "T1" ."TEXT" END) "N5" 
FROM "V_QUOTE_NOTES" "T1"
GROUP BY
"T1" ."QUOTE_LINE" ,
"T1" ."QUOTE_NO"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources