February 7, 2019 at 9:50 am
Data in SQL table is
| Cust_Name | Prd_Name | 1/1/2019 | 2/1/2019 | 3/1/2019 |
| John | Mobile | 18.5 | 45.7 | 66.9 |
| Scott | Laptop | 9.5 | 3.7 | 0 |
I want to unpivot columns [1/1/2019], [2/1/2019], [3/1/2019] and want the result as below
| Cust_Name | Prd_Name | Sales_Month | Value |
| John | Mobile | 1/1/2019 | 18.5 |
| John | Mobile | 2/1/2019 | 45.7 |
| John | Mobile | 3/1/2019 | 66.9 |
| Scott | Laptop | 1/1/2019 | 9.5 |
| Scott | Laptop | 2/1/2019 | 3.7 |
| Scott | Laptop | 3/1/2019 | 0 |
How can I do this? Also, the number of such columns which I want to unpivot is dynamic so it starts with 1/1/2019, 2/1/2019,....12/1/2019
February 7, 2019 at 10:04 am
Does this need to be dynamic? Are you adding new columns to your table each month (so next month you'll have a column 04/01/2019)? If so, I'd suggest that what you need to do isn't create a dynamic pivot, you need to fix your database design so that it's like the data in your second dataset. Or is that your aim of this? (But then like I said, there's no need for dynamic SQL.)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 7, 2019 at 10:12 am
Thom A - Thursday, February 7, 2019 10:04 AMDoes this need to be dynamic? Are you adding new columns to your table each month (so next month you'll have a column 04/01/2019)? If so, I'd suggest that what you need to do isn't create a dynamic pivot, you need to fix your database design so that it's like the data in your second dataset. Or is that your aim of this? (But then like I said, there's no need for dynamic SQL.)
Yes, a new column will be added like 4/1/2019, 5/1/2019...
And cannot change database design as this is how I get data, so I want to create SQL query (dynamic is one approach that I see) which will handle this and create data like the expected second dataset.
February 7, 2019 at 10:19 am
This is a way of doing it. It would be great if you could avoid this problem and get the unpivoted data to begin with, but that's something that might not be in your hands.
CREATE TABLE SampleData(
Cust_Name varchar(100),
Prd_Name varchar(100),
[1/1/2019] decimal(10, 4),
[2/1/2019] decimal(10, 4),
[3/1/2019] decimal(10, 4)
)
INSERT INTO SampleData
VALUES
('John', 'Mobile', 18.5, 45.7, 66.9),
('Scott', 'Laptop', 9.5, 3.7, 0);
DECLARE @Columns NVARCHAR(MAX),
@SQL NVARCHAR(MAX);
SELECT @Columns = STUFF(( SELECT CHAR(9) + ',( CAST( ' + QUOTENAME(name, '''') + ' AS date), ' + QUOTENAME(name) + ')' + CHAR(13)
FROM sys.columns c
WHERE object_id = OBJECT_ID('SampleData')
AND c.name NOT LIKE '%[^0-9/]%'
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 2, '')
SET @SQL = N'SELECT Cust_Name, Prd_Name, SalesMonth, Value
FROM SampleData
CROSS APPLY (VALUES ' + @Columns + ')u( SalesMonth, Value)'
PRINT @SQL;
EXEC(@SQL)
GO
DROP TABLE SampleData
February 7, 2019 at 2:43 pm
Luis Cazares - Thursday, February 7, 2019 10:19 AMThis is a way of doing it. It would be great if you could avoid this problem and get the unpivoted data to begin with, but that's something that might not be in your hands.
CREATE TABLE SampleData(
Cust_Name varchar(100),
Prd_Name varchar(100),
[1/1/2019] decimal(10, 4),
[2/1/2019] decimal(10, 4),
[3/1/2019] decimal(10, 4)
)
INSERT INTO SampleData
VALUES
('John', 'Mobile', 18.5, 45.7, 66.9),
('Scott', 'Laptop', 9.5, 3.7, 0);DECLARE @Columns NVARCHAR(MAX),
@SQL NVARCHAR(MAX);SELECT @Columns = STUFF(( SELECT CHAR(9) + ',( CAST( ' + QUOTENAME(name, '''') + ' AS date), ' + QUOTENAME(name) + ')' + CHAR(13)
FROM sys.columns c
WHERE object_id = OBJECT_ID('SampleData')
AND c.name NOT LIKE '%[^0-9/]%'
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 2, '')
SET @SQL = N'SELECT Cust_Name, Prd_Name, SalesMonth, Value
FROM SampleData
CROSS APPLY (VALUES ' + @Columns + ')u( SalesMonth, Value)'PRINT @SQL;
EXEC(@SQL)
GO
DROP TABLE SampleData
Thanks a lot, it worked. But I am new to SQL. Can you please help me to understand what exactly this 2 lines do
STUFF(( SELECT CHAR(9) + ',( CAST( ' + QUOTENAME(name, '''') + ' AS date), ' + QUOTENAME(name) + ')' + CHAR(13)
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 2, '')
February 7, 2019 at 2:53 pm
vikasjagadale8 - Thursday, February 7, 2019 2:43 PMThanks a lot, it worked. But I am new to SQL. Can you please help me to understand what exactly this 2 lines doSTUFF(( SELECT CHAR(9) + ',( CAST( ' + QUOTENAME(name, '''') + ' AS date), ' + QUOTENAME(name) + ')' + CHAR(13)
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 2, '')
To fully understand the solution, please read the following articles:
Creating a concatenated string using FOR XML
Using CROSS APPLY VALUES to unpivot
February 7, 2019 at 10:18 pm
Luis Cazares - Thursday, February 7, 2019 2:53 PMvikasjagadale8 - Thursday, February 7, 2019 2:43 PMThanks a lot, it worked. But I am new to SQL. Can you please help me to understand what exactly this 2 lines doSTUFF(( SELECT CHAR(9) + ',( CAST( ' + QUOTENAME(name, '''') + ' AS date), ' + QUOTENAME(name) + ')' + CHAR(13)
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 2, '')
To fully understand the solution, please read the following articles:
Creating a concatenated string using FOR XML
Using CROSS APPLY VALUES to unpivot
Thanks again for the links
February 11, 2019 at 3:11 am
/*
CREATE TABLE SampleData(
Cust_Name varchar(100),
Prd_Name varchar(100),
[1/1/2019] decimal(10, 4),
[2/1/2019] decimal(10, 4),
[3/1/2019] decimal(10, 4)
)
INSERT INTO SampleData
VALUES
('John', 'Mobile', 18.5, 45.7, 66.9),
('Scott', 'Laptop', 9.5, 3.7, 0);
*/
Declare @sql varchar(max)='',@column_name varchar(max)=''
Select @column_name =@column_name +',['+column_name+']' from INFORMATION_SCHEMA.columns where table_name ='SampleData' and COLUMN_NAME not in ('Cust_Name','Prd_Name')
set @sql =
'
Select * from SampleData as a
unpivot
(
Month for valu in ('+stuff(@column_name,1,1,'')+')
) as b'
exec(@sql)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply