Problem concatenating rows - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem concatenating rows

Author

Topic

ChadMckenzie
Starting Member

2 Posts

Posted - 2009-02-27 : 16:17:10

I have been working on this problem a couple hours now, and I've done more complicated sql than this...and yet I'm stuck. Basically here's what I have.. I have written a sql statement that will return the following results:

Invoice udid1 udid2 udid3 udid4
1 x NULL NULL NULL
1 NULL b NULL NULL
1 NULL NULL l NULL
1 NULL NULL NULL l
2 x NULL NULL NULL
2 NULL b NULL NULL

what I NEED is one row for each invoice (i.e)

Invoice udid1 udid2 udid3 udid4
1 x b l l
2 x b null null

I know this is a simple answer and I'm just having a brain freeze at the moment. Any help would be appreciated.

For information purposes, here is the sql that gives me the results I have currently:

SELECT InvoiceNumber, CASE commentnumber WHEN 14 THEN 'x' ELSE NULL END AS udid1, CASE commentnumber WHEN 15 THEN 'b' ELSE NULL END AS udid2, CASE commentnumber WHEN 16 THEN 'l' ELSE NULL END AS udid3, CASE commentnumber WHEN 17 THEN 'l' ELSE NULL END AS udid4
FROM (SELECT Invoice.InvoiceNumber, comments.Commentnumber, comments.commentcode
FROM Invoice INNER JOIN
comments ON Invoice.InvoiceNumber = comments.InvoiceNumber) AS derivedtbl_1

and here are the source tables (this is test data, im trying to get the idea straight before I code against my real tables)

invoice table

Invoicenumber
1
2

comments table
invoicenumber commentnumber
1 14
1 15
1 16
1 17
2 14
2 15

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-27 : 16:35:41

Try this

SELECT InvoiceNumber, CASE commentnumber WHEN 14 THEN 'x' ELSE NULL END AS udid1, CASE commentnumber WHEN 15 THEN 'b' ELSE NULL END AS udid2, CASE commentnumber WHEN 16 THEN 'l' ELSE NULL END AS udid3, CASE commentnumber WHEN 17 THEN 'l' ELSE NULL END AS udid4
FROM (SELECT Invoice.InvoiceNumber, comments.Commentnumber, comments.commentcode
FROM Invoice INNER JOIN
comments ON Invoice.InvoiceNumber = comments.InvoiceNumber) AS derivedtbl_1
GROUP BY InvoiceNumber

ChadMckenzie
Starting Member

2 Posts

Posted - 2009-03-02 : 09:46:17

Yeah that was my first thought too, but group by there results in 'commentnumber is an invalid field because it is not in a group by or aggregate function'

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-02 : 09:59:57

group by invoiceNumber, then use max() with everything else... that will get rid of the nulls

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 10:04:44

[code]
SELECT InvoiceNumber,
MAX(CASE commentnumber WHEN 14 THEN 'x' ELSE NULL END) AS udid1,
MAX(CASE commentnumber WHEN 15 THEN 'b' ELSE NULL END) AS udid2,
MAX(CASE commentnumber WHEN 16 THEN 'l' ELSE NULL END) AS udid3,
MAX(CASE commentnumber WHEN 17 THEN 'l' ELSE NULL END) AS udid4
FROM (SELECT Invoice.InvoiceNumber, comments.Commentnumber, comments.commentcode
FROM Invoice INNER JOIN
comments ON Invoice.InvoiceNumber = comments.InvoiceNumber) AS derivedtbl_1
GROUP BY InvoiceNumber
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources