CODEWITHSUNDEEP
sqlsql-server-2008
user2770323
user2770323

Reputation: 1

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

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!

Upvotes: 0

Views: 85

Answers (1)

Lamak
Lamak

Reputation: 70638

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;

Upvotes: 1

Related Questions