SQL - multiple rows with one column into one row with multiple columns

Question

I have the following query:

select convert(varchar(32),tas.complete_by_dt,101)+': '+tas.notes [Task] 
from TX_SOL_TASK tas where customer_no=230459

and it is returning one row for each value, while I wish to just have one row with a column for each value. Here is was what currently returns:

Task

08/07/2013: Called Jane for lunch of she is in town

08/19/2013: Jane is NY and will talk with her acct the end of August. Will know then escat amount and hoping by the end of 1st qtr.

09/09/2013: Jane called and requested info to send her check through Fidelity. She is at Canyon Ranch this week. Emailed her info.

09/24/2013: Thank you!

11/06/2013: Called Jane for lunch with MD and mf, she is trasveling after this week. Call her 12/9 to see if we can do that week!

11/13/2013: Sent Jane happy thought for another sucessful Hats in The Garden!

For SQL, if you want to turn rows into columns you'll want to read more about PIVOT functionality. — Goose
– Goose, Commented Jan 3, 2014 at 18:30

Lamak · Accepted Answer · 2014-01-03 18:37:17Z

1

Try this dynamic pivot:

DECLARE @customer_no INT = 230459;

DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';

SELECT @cols += STUFF((SELECT ',' + QUOTENAME(CONVERT(VARCHAR(32),complete_by_dt,101))
                       FROM dbo.TX_SOL_TASK 
                       WHERE customer_no = @customer_no
                       GROUP BY CONVERT(VARCHAR(32),complete_by_dt,101)
                       FOR XML PATH('')), 1, 1, '');

SET @sql = N'SELECT *
  FROM (SELECT  CONVERT(VARCHAR(32),complete_by_dt,101) Completed,
                notes
        FROM dbo.TX_SOL_TASK
        WHERE customer_no = @customer_no
        ) AS d
  PIVOT (MIN([notes]) FOR [Completed] IN (' + @cols + ')) AS p;';

EXEC sp_executesql @sql, N'@customer_no INT', @customer_no;

answered Jan 3, 2014 at 18:37

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - multiple rows with one column into one row with multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related