August 18, 2022 at 4:17 pm
Here is the DDL of the table:
I need single row per invoicenumber and line and rest of the amount and comments should to go to additional columns in the same row as shown (in the spreadsheet).
What would be the best way to accomplish this?
DROP TABLE IF EXISTS #temp1
CREATE TABLE #temp1 (Program VARCHAR(10), Invnumbe INT, LinNumber INT, Amount DECIMAL(12,0), comments VARCHAR(100), DataSource VARCHAR(10))
INSERT INTO #temp1 VALUES(552,1001,1,20.00, 'Program1', 'SMS')
INSERT INTO #temp1 VALUES(662,1001,1,30.00, 'Program2', 'SMS1')
INSERT INTO #temp1 VALUES(663,1001,2,40.00, 'Program1', 'SMS2')
INSERT INTO #temp1 VALUES(664,1001,2,45.00, 'Program2', 'SMS3')
INSERT INTO #temp1 VALUES(665,1001,2,60.00, 'Program3', 'SMS4')
INSERT INTO #temp1 VALUES(666,1001,2,70.00, 'Program4', 'SMS3')
INSERT INTO #temp1 VALUES(667,1001,2,80.00, 'Program5', 'SMS4')
August 18, 2022 at 5:07 pm
This can be achieved using a Cross-Tab query.
Static and Dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
SELECT InvoiceNumber = src.Invnumbe
, LineNumber = src.LinNumber
, Program1 = MAX(CASE WHEN src.comments = 'Program1' THEN src.comments END)
, Amount1 = MAX(CASE WHEN src.comments = 'Program1' THEN src.Amount END)
, Program2 = MAX(CASE WHEN src.comments = 'Program2' THEN src.comments END)
, Amount2 = MAX(CASE WHEN src.comments = 'Program2' THEN src.Amount END)
, Program3 = MAX(CASE WHEN src.comments = 'Program3' THEN src.comments END)
, Amount3 = MAX(CASE WHEN src.comments = 'Program3' THEN src.Amount END)
, Program4 = MAX(CASE WHEN src.comments = 'Program4' THEN src.comments END)
, Amount4 = MAX(CASE WHEN src.comments = 'Program4' THEN src.Amount END)
, Program5 = MAX(CASE WHEN src.comments = 'Program5' THEN src.comments END)
, Amount5 = MAX(CASE WHEN src.comments = 'Program5' THEN src.Amount END)
FROM #temp1 as src
GROUP BY src.Invnumbe, src.LinNumber;
August 18, 2022 at 5:07 pm
I need single row per invoicenumber
Yet you're showing two lines of output for the same invoice number??
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 18, 2022 at 6:26 pm
Soultuion DesNorton gave worked like a champ. Thank you so much!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply