1

I have a table being returned from a sql stored procedure that returns Date, Name, Subject, and Amount. I want each distinct date that is returned to be a column in the output. So, say the date range I pass the stored procedure is 1/1/2013 to 1/5/2013. I would want the table to be returned as Name, Subject, 1/1/2013, 1/2/2013, 1/3/2013, 1/4/2013, and 1/5/2013. How can I go about this?

Sean Smyth
  • 1,509
  • 3
  • 25
  • 43
  • Which version of SQL Server (if 2005 or higher), you can use the PIVOT command – Sparky Dec 19 '13 at 18:58
  • How does your output look like? How did you try to output it in desired way? – BartekR Dec 19 '13 at 19:13
  • @Sparky I'm reading up on the pivot command right now. But every example I have, they have an idea of which rows need to be pivoted. In my case I have no idea what date value I'm going to get. How could I do this in my case? – Sean Smyth Dec 19 '13 at 19:14
  • do you want the dates to be column headers or actual data for each row. – stackuser Dec 19 '13 at 19:59
  • Try this http://stackoverflow.com/questions/18317732/sql-server-pivot-table-with-joins-and-dynamic-columns question – Sparky Dec 19 '13 at 21:12
  • @stackuser I want the dates to go from being data in a table to being column headers – Sean Smyth Dec 19 '13 at 22:45

1 Answers1

0

SELECT date,name,subject,amount into #tmp from [your table]

DECLARE @DDATE DATE DECLARE @SQLQ NVARCHAR(500)

DECLARE DT_CURSOR CURSOR FOR SELECT DISTINCT DATE FROM #tmp OPEN DT_CURSOR FETCH NEXT FROM DT_CURSOR INTO @DDATE WHILE @@FETCH_STATUS=0 BEGIN SET @SQLQ = 'ALTER TABLE #TMP ADD ['+REPLACE(RTRIM(LTRIM(CONVERT(VARCHAR(50),REPLACE(@DDATE,'-','')))),' ','')+'] VARCHAR(50)' EXECUTE sp_executesql @SQLQ FETCH NEXT FROM DT_CURSOR INTO @DDATE END CLOSE DT_CURSOR DEALLOCATE DT_CURSOR SELECT * FROM #TMP DROP TABLE #TMP

You have not told how the data should group in new columns. this just creates Column for every distinct date.

Sandeep Mogaveer
  • 57
  • 1
  • 12