We help IT Professionals succeed at work.

Pivot this table (column)

I wonder if this is doable...I tried a pivot example and couldn't get it working. If not doable in SQL, I'll look into doing it in C#.

1. This is sample data. I have "12345" in the usage column as example.

CREATE TABLE #temptable ( [activationdate] nvarchar(20), [reportdate] nvarchar(20), [usage] money )
INSERT INTO #temptable
VALUES
( N'2018.1', N'2019.5', 12345.0000 ), 
( N'2018.2', N'2019.5', 12345.0000 ), 
( N'2018.3', N'2019.5', 12345.0000 ), 
( N'2018.4', N'2019.5', 12345.0000 ), 
( N'2018.5', N'2019.5', 12345.0000 ), 
( N'2018.6', N'2019.5', 12345.0000 ), 
( N'2018.7', N'2019.5', 12345.0000 ), 
( N'2018.8', N'2019.5', 12345.0000 ), 
( N'2018.9', N'2019.5', 12345.0000 ), 
( N'2018.10', N'2019.5', 12345.0000 ), 
( N'2018.11', N'2019.5', 12345.0000 ), 
( N'2018.12', N'2019.5', 12345.0000 ), 
( N'2019.1', N'2019.5', 12345.0000 ), 
( N'2019.2', N'2019.5', 12345.0000 ), 
( N'2019.3', N'2019.5', 12345.0000 ), 
( N'2019.4', N'2019.5', 12345.0000 ), 
( N'2019.5', N'2019.5', 12345.0000 ), 
( N'2019.6', N'2019.5', 12345.0000 ), 
( N'2019.7', N'2019.5', 12345.0000 ), 
( N'2019.8', N'2019.5', 12345.0000 ), 
( N'2019.9', N'2019.5', 12345.0000 ), 
( N'2019.10', N'2019.5', 12345.0000 ), 
( N'2018.1', N'2019.6', 12345.0000 ), 
( N'2018.2', N'2019.6', 12345.0000 ), 
( N'2018.3', N'2019.6', 12345.0000 ), 
( N'2018.4', N'2019.6', 12345.0000 ), 
( N'2018.5', N'2019.6', 12345.0000 ), 
( N'2018.6', N'2019.6', 12345.0000 ), 
( N'2018.7', N'2019.6', 12345.0000 ), 
( N'2018.8', N'2019.6', 12345.0000 ), 
( N'2018.9', N'2019.6', 12345.0000 ), 
( N'2018.10', N'2019.6', 12345.0000 ), 
( N'2018.11', N'2019.6', 12345.0000 ), 
( N'2018.12', N'2019.6', 12345.0000 ), 
( N'2019.1', N'2019.6', 12345.0000 ), 
( N'2019.2', N'2019.6', 12345.0000 ), 
( N'2019.3', N'2019.6', 12345.0000 ), 
( N'2019.4', N'2019.6', 12345.0000 ), 
( N'2019.5', N'2019.6', 12345.0000 ), 
( N'2019.6', N'2019.6', 12345.0000 ), 
( N'2019.7', N'2019.6', 12345.0000 ), 
( N'2019.8', N'2019.6', 12345.0000 ), 
( N'2019.9', N'2019.6', 12345.0000 ), 
( N'2019.10', N'2019.6', 12345.0000 ), 
( N'2018.1', N'2019.7', 12345.0000 ), 
( N'2018.2', N'2019.7', 12345.0000 ), 
( N'2018.3', N'2019.7', 12345.0000 ), 
( N'2018.4', N'2019.7', 12345.0000 ), 
( N'2018.5', N'2019.7', 12345.0000 ), 
( N'2018.6', N'2019.7', 12345.0000 ), 
( N'2018.7', N'2019.7', 12345.0000 ), 
( N'2018.8', N'2019.7', 12345.0000 ), 
( N'2018.9', N'2019.7', 12345.0000 ), 
( N'2018.10', N'2019.7', 12345.0000 ), 
( N'2018.11', N'2019.7', 12345.0000 ), 
( N'2018.12', N'2019.7', 12345.0000 ), 
( N'2019.1', N'2019.7', 12345.0000 ), 
( N'2019.2', N'2019.7', 12345.0000 ), 
( N'2019.3', N'2019.7', 12345.0000 ), 
( N'2019.4', N'2019.7', 12345.0000 ), 
( N'2019.5', N'2019.7', 12345.0000 ), 
( N'2019.6', N'2019.7', 12345.0000 ), 
( N'2019.7', N'2019.7', 12345.0000 ), 
( N'2019.8', N'2019.7', 12345.0000 ), 
( N'2019.9', N'2019.7', 12345.0000 ), 
( N'2019.10', N'2019.7', 12345.0000 ), 
( N'2018.1', N'2019.8', 12345.0000 ), 
( N'2018.2', N'2019.8', 12345.0000 ), 
( N'2018.3', N'2019.8', 12345.0000 ), 
( N'2018.4', N'2019.8', 12345.0000 ), 
( N'2018.5', N'2019.8', 12345.0000 ), 
( N'2018.6', N'2019.8', 12345.0000 ), 
( N'2018.7', N'2019.8', 12345.0000 ), 
( N'2018.8', N'2019.8', 12345.0000 ), 
( N'2018.9', N'2019.8', 12345.0000 ), 
( N'2018.10', N'2019.8', 12345.0000 ), 
( N'2018.11', N'2019.8', 12345.0000 ), 
( N'2018.12', N'2019.8', 12345.0000 ), 
( N'2019.1', N'2019.8', 12345.0000 ), 
( N'2019.2', N'2019.8', 12345.0000 ), 
( N'2019.3', N'2019.8', 12345.0000 ), 
( N'2019.4', N'2019.8', 12345.0000 ), 
( N'2019.5', N'2019.8', 12345.0000 ), 
( N'2019.6', N'2019.8', 12345.0000 ), 
( N'2019.7', N'2019.8', 12345.0000 ), 
( N'2019.8', N'2019.8', 12345.0000 ), 
( N'2019.9', N'2019.8', 12345.0000 ), 
( N'2019.10', N'2019.8', 12345.0000 ), 
( N'2018.1', N'2019.9', 12345.0000 ), 
( N'2018.2', N'2019.9', 12345.0000 ), 
( N'2018.3', N'2019.9', 12345.0000 ), 
( N'2018.4', N'2019.9', 12345.0000 ), 
( N'2018.5', N'2019.9', 12345.0000 ), 
( N'2018.6', N'2019.9', 12345.0000 ), 
( N'2018.7', N'2019.9', 12345.0000 ), 
( N'2018.8', N'2019.9', 12345.0000 ), 
( N'2018.9', N'2019.9', 12345.0000 ), 
( N'2018.10', N'2019.9', 12345.0000 ), 
( N'2018.11', N'2019.9', 12345.0000 ), 
( N'2018.12', N'2019.9', 12345.0000 ), 
( N'2019.1', N'2019.9', 12345.0000 ), 
( N'2019.2', N'2019.9', 12345.0000 ), 
( N'2019.3', N'2019.9', 12345.0000 ), 
( N'2019.4', N'2019.9', 12345.0000 ), 
( N'2019.5', N'2019.9', 12345.0000 ), 
( N'2019.6', N'2019.9', 12345.0000 ), 
( N'2019.7', N'2019.9', 12345.0000 ), 
( N'2019.8', N'2019.9', 12345.0000 ), 
( N'2019.9', N'2019.9', 12345.0000 ), 
( N'2019.10', N'2019.9', 12345.0000 ), 
( N'2018.1', N'2019.10', 12345.0000 ), 
( N'2018.2', N'2019.10', 12345.0000 ), 
( N'2018.3', N'2019.10', 12345.0000 ), 
( N'2018.4', N'2019.10', 12345.0000 ), 
( N'2018.5', N'2019.10', 12345.0000 ), 
( N'2018.6', N'2019.10', 12345.0000 ), 
( N'2018.7', N'2019.10', 12345.0000 ), 
( N'2018.8', N'2019.10', 12345.0000 ), 
( N'2018.9', N'2019.10', 12345.0000 ), 
( N'2018.10', N'2019.10', 12345.0000 ), 
( N'2018.11', N'2019.10', 12345.0000 ), 
( N'2018.12', N'2019.10', 12345.0000 ), 
( N'2019.1', N'2019.10', 12345.0000 ), 
( N'2019.2', N'2019.10', 12345.0000 ), 
( N'2019.3', N'2019.10', 12345.0000 ), 
( N'2019.4', N'2019.10', 12345.0000 ), 
( N'2019.5', N'2019.10', 12345.0000 ), 
( N'2019.6', N'2019.10', 12345.0000 ), 
( N'2019.7', N'2019.10', 12345.0000 ), 
( N'2019.8', N'2019.10', 12345.0000 ), 
( N'2019.9', N'2019.10', 12345.0000 ), 
( N'2019.10', N'2019.10', 12345.0000 )

DROP TABLE #temptable

Open in new window


2. I want to present the data like this. The" activationdate" column is fine. The "report date" column needs to be a row on top and usage values go under corresponding "report date".
 For example, if you look at the first row in the #temptable.... it means... for activation date of 2018.1 and report column of 2015, the usage is 12345

for-ee.png
3. This is the SQL that produces the data in #temptable

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
             
			 
				  INSERT INTO #usage (reportdate,activationdate,usage)
			       SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits)
						  FROM  
				        (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							FROM [DebitCard_Data].[dbo].[DC_DebitCard_Payment] dcp
								INNER JOIN DC_Balance db
									ON db.Account = dcp.Account 
								INNER JOIN #TransIDs tid
									ON dcp.[Transaction ID] = tid.[Transaction ID]
							WHERE YEAR(tid.[BusinessDate]) = @rYear
								  AND MONTH(tid.[BusinessDate]) = @rMonth

								  -- This is the down chink
								  AND YEAR(db.ActivationDate) = @aYear --*** use activation date
								  AND MONTH(db.ActivationDate) = @aMonth
							 --first sum
                            
						UNION ALL
						SELECT SUM([CashBalance]) TotalDebits
						FROM [DebitCard_Data].[dbo].[DC_ClearCardHistory] dc
							INNER JOIN DC_Balance db
								ON db.Account = dc.Account 
							
							--INNER JOIN #TransIDs tid
							--	ON dcp.[Transaction ID] = tid.[Transaction ID]
						WHERE
							-- This is the down chink  
							YEAR(db.ActivationDate) >= @aYear
							AND MONTH(db.ActivationDate) <= @aMonth

							-- This is the across chink
							AND YEAR([LT_DateTime]) > DATEADD(HOUR, 7, @rYear)
							AND MONTH([LT_DateTime]) < DATEADD(HOUR, 7, @rMonth)
							 
                       ) AS debitsums  -- union statement
							
					Fetch Next from activationCursor into @aYear, @aMonth
				END
                
				
			Close activationCursor
			DEALLOCATE activationCursor
		
		
		 
		 Fetch Next from reportCursor into @rYear, @rMonth	
	End
	Close reportCursor
	DEALLOCATE reportCursor

Open in new window

Comment
Watch Question

Co-Founder and Chief Architect
CERTIFIED EXPERT
Top Expert 2016
Commented:
You'll need to generate a dynamic query to pivot on unknown column names.  Thanks for the sample data!

Declare 2 variables-- one for the column names and one for the query itself.
DECLARE @columnnames NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

Open in new window


Then we need to get a list of the Column names.  So we select the distinct report dates and then to get the name of the month we use DATENAME().  If you want to filter on criteria, you should also specify it here so you limit the number of selects going on.
SELECT @columnnames = ISNULL(@columnnames + ',','') + '[' + CAST(reportdate AS NVARCHAR(MAX)) 
	+ DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+'-1-2000') + ']'
FROM (SELECT DISTINCT reportdate FROM #temptable) x

Open in new window


Now we need the query.  So we're basically going to use the @columnnames variable and also when we are selecting report date here, notice I'm doing DATENAME() again to get the name of the month like in the example.
SET @query = 
	N'SELECT activationdate,'+@columnnames+' FROM
	(
		SELECT activationdate,reportdate + DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+''-1-2000'') AS reportdate,usage
		FROM #temptable
	) AS t
	PIVOT ( MAX(usage) FOR reportdate IN ('+@columnnames+')) AS p'

Open in new window


So, now you can run this whole block to view what the column names look like and the query that gets generated.
DECLARE @columnnames NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SELECT @columnnames = ISNULL(@columnnames + ',','') + '[' + CAST(reportdate AS NVARCHAR(MAX)) 
	+ DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+'-1-2000') + ']'
FROM (SELECT DISTINCT reportdate FROM #temptable) x

SET @query = 
	N'SELECT activationdate,'+@columnnames+' FROM
	(
		SELECT activationdate,reportdate + DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+''-1-2000'') AS reportdate,usage
		FROM #temptable
	) AS t
	PIVOT ( MAX(usage) FOR reportdate IN ('+@columnnames+')) AS p'

SELECT @columnnames
SELECT @query

Open in new window


That's just to help understand what is being created.  But the column names will look like
[2019.10October],[2019.5May],[2019.6June],[2019.7July],[2019.8August],[2019.9September]
and the query will look like
SELECT activationdate,[2019.10October],[2019.5May],[2019.6June],[2019.7July],[2019.8August],[2019.9September] FROM   (    SELECT activationdate,reportdate + DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+'-1-2000') AS reportdate,usage    FROM #temptable   ) AS t   PIVOT ( MAX(usage) FOR reportdate IN ([2019.10October],[2019.5May],[2019.6June],[2019.7July],[2019.8August],[2019.9September])) AS p

To view the results run:
DECLARE @columnnames NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SELECT @columnnames = ISNULL(@columnnames + ',','') + '[' + CAST(reportdate AS NVARCHAR(MAX)) 
	+ DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+'-1-2000') + ']'
FROM (SELECT DISTINCT reportdate FROM #temptable) x

SET @query = 
	N'SELECT activationdate,'+@columnnames+' FROM
	(
		SELECT activationdate,reportdate + DATENAME(m, SUBSTRING(reportdate,6,LEN(reportdate)+1)+''-1-2000'') AS reportdate,usage
		FROM #temptable
	) AS t
	PIVOT ( MAX(usage) FOR reportdate IN ('+@columnnames+')) AS p'

EXEC sp_executesql @query

Open in new window


Results:
29161741_1.png
CERTIFIED EXPERT

Author

Commented:
Thanks! let me try in the morning. Thanks for taking the time and doing this.
CERTIFIED EXPERT

Author

Commented:
Thanks, Dustin. Seems to be working. If I need more help, I'll post back here.
CERTIFIED EXPERT

Author

Commented:
How can I add an "order by activationdate" to the query. You see how the dates are coming out...I want it to be in order

2006.9
2006.10
2006.11
2006.12
and so on

for-ee2.png
CERTIFIED EXPERT

Author

Commented:
I tried this but still get the same result

PIVOT ( MAX(usage) FOR reportdate IN ('+@columnnames+')) AS p order by activationdate asc '

This is how the #temptable is defined

--create table  #temptable
--(
--      ReportDate nvarchar(20)
--      ,ActivationDate nvarchar(20)
--      ,Usage money
-- )
CERTIFIED EXPERT

Author

Commented:
I did this but now the years are not together

for-ee3.png
CERTIFIED EXPERT

Author

Commented:
I got it. I updated the temp table like this and then ran your query. It works now.

UPDATE
   #usage
SET
   activationdate =SUBSTRING(activationdate,0, CHARINDEX('.',activationdate)) + '.'+ '0' + right(activationdate, charindex('.', reverse(activationdate)) - 1)
WHERE
   LEN(right(activationdate, charindex('.', reverse(activationdate)) - 1)) = 1;
Dustin SaundersCo-Founder and Chief Architect
CERTIFIED EXPERT
Top Expert 2016

Commented:
Hey Camillia,

Sorry, wasn't available yesterday but glad to see you got it going!