Click here to Skip to main content
15,311,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I have a scenario where I have to use the row as a column but dynamically
 ID Bussiness 	   Date	         Value
1     	GPS	     Nov-18	         3
2     	GPS	     Dec-18	         2
3     	GPS	     Nov-18	         2
4     	GPS	     Dec-18	         3
and my aim is to have a out put like below;

ID	       Bussiness 	Nov-18	Dec-18
1 	        GPS	          3	    2
2	        GPS	          2	    3


DDL:
CREATE TABLE mytable(
   ID   INT   NOT NULL   
  ,Bussiness VARCHAR(3) NOT NULL
  ,Date VARCHAR(10) NOT NULL
  ,Value      INT  NOT NULL
  
);
INSERT INTO mytable( ID,Bussiness,Date, Value) VALUES
 (1,'GPS','Nov-18',3 )
,(2,'GPS','Dec-18',2 )
,(3,'GPS','Nov-18',2 )
,(4,'GPS','Dec-18',3 );

What I have tried:

<pre>go
;with cte as(
select row_number()over(partition by date order by ID)ID,
Bussiness,Date, Value 
from mytable
)
SELECT *
FROM cte
PIVOT (
	MAX([Value])
	FOR Date IN ( [Nov-18],[Dec-18])
) AS pvt
Posted
Updated 11-Dec-19 2:33am

The problem you are having is not entirely code based, it is a problem with using reserved/special names for your columns.

One of the rules that I use is that if a particular column-name displays in a different color than the rest in an intellisense environment, it either needs to be changed or properly escaped.
Notice that even in the CP editor which does not have all of the SQL definitions in it that "Date" is a different color. In my local editor "Value" also changes color
SQL
CREATE TABLE mytable(
   ID   INT   NOT NULL   
  ,Bussiness VARCHAR(3) NOT NULL
  ,Date VARCHAR(10) NOT NULL
  ,Value      INT  NOT NULL
  
);
My normal routine would be then to change the names of the offending columns; so that is what I did in this test of your code and it all worked out, and I got the results you desired
SQL
DECLARE @mytable TABLE (
   ID   		INT   NOT NULL   
  ,Bussiness	VARCHAR(3) NOT NULL
  ,mDate		VARCHAR(10) NOT NULL
  ,mValue		INT  NOT NULL
  
);
INSERT @mytable( ID,Bussiness, mDate, mValue) VALUES
 (1,'GPS','Nov-18',3 )
,(2,'GPS','Dec-18',2 )
,(3,'GPS','Nov-18',2 )
,(4,'GPS','Dec-18',3 );

;with cte as(
	select row_number()over(partition by mDate order by ID) ID,
	Bussiness, mDate, mValue 
from @mytable
)

SELECT	*
FROM		cte
PIVOT (
	MAX([mValue])
	FOR mDate IN ( [Nov-18],[Dec-18])
) AS pvt
If you cannot change the names, then you need to wrap the offending columns in [square brackets], and running this code also produces the desired results
SQL
DECLARE @mytable TABLE (
   ID   		INT   NOT NULL   
  ,Bussiness	VARCHAR(3) NOT NULL
  ,[Date]		VARCHAR(10) NOT NULL
  ,[Value]		INT  NOT NULL
  
);
INSERT @mytable( ID,Bussiness, [Date], [Value]) VALUES
 (1,'GPS','Nov-18',3 )
,(2,'GPS','Dec-18',2 )
,(3,'GPS','Nov-18',2 )
,(4,'GPS','Dec-18',3 );

;with cte as(
	select row_number()over(partition by [Date] order by ID) ID,
	Bussiness, [Date], [Value] 
from @mytable
)

SELECT	*
FROM		cte
PIVOT (
	MAX([Value])
	FOR [Date] IN ( [Nov-18],[Dec-18])
) AS pvt

And while it is not causing a problem in this instance; you should always use the proper Data Types; storing dates as text removes a lot of the features of the Date type, such as sorting and arithmetic operations.
   
Comments
Maciej Los 10-Dec-19 11:29am
   
5ed!
Member 14684664 11-Dec-19 10:59am
   
By removing the comment it has deleted your your answer too sadly,
so my question was If I already have the table populated how do i run the code from ?
MadMyche 11-Dec-19 11:18am
   
My example used a @Table declared at run time so that I did not retain the data on my server; you can just start with the CTE and adjust to match your table/column names as prescribed
Member 14684664 11-Dec-19 12:19pm
   
Thanks again , I have applied your advice to the columns and I am getting below error, any idea what I am doing wrong
Msg 488, Level 16, State 1, Line 51
Pivot grouping columns must be comparable. The type of column "description" is "text", which is not comparable.
MadMyche 11-Dec-19 12:29pm
   
Data types TEXT and NTEXT cannot be used within Pivots
Member 14684664 12-Dec-19 10:48am
   
thanks
   
Comments
Member 14684664 11-Dec-19 8:34am
   
Thanks for the post ,
I have tried your steps first for the static to get the result before I move to the dynamic one but I get below error
"
Hide   Copy Code
Msg 488, Level 16, State 1, Line 51
Pivot grouping columns must be comparable. The type of column "description" is "text", which is not comparable.


"
any advice ?
thanks
Maciej Los 12-Dec-19 12:54pm
   
Sorry, but i can't read in your mind or direct from your screen...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900