September 6, 2010 at 7:06 am
I have a table that looks like:
DECLARE @params AS TABLE
(
ParameterID NVARCHAR(8) NOT NULL,
ParameterValue SQL_VARIANT NOT NULL,
ParameterTimestamp DATETIME NOT NULL
)
INSERT INTO @params( ParameterID, ParameterValue, ParameterTimestamp)
SELECT 'P1', '111', '20100801' UNION
SELECT 'P1', '222', '20100803' UNION
SELECT 'P1', 'aaa', '20100808' UNION
SELECT 'P2', 'bbb', '20100809' UNION
SELECT 'P5', 'ccc', '20100801' UNION
SELECT 'P5', 'sss', '20100803' UNION
SELECT 'P5', 'ttt', '20100808' UNION
SELECT 'P6', 'zzz', '20100808'
I need a query that pivot the rows from @params table. The result should looks like:
SELECT '20100801' AS ParameterTimestamp, '111' AS P1, NULL AS P2, 'ccc' AS P5, NULL AS P6 UNION
SELECT '20100803' AS ParameterTimestamp, '222' AS P1, NULL AS P2, 'ccc' AS P5, NULL AS P6 UNION
SELECT '20100808' AS ParameterTimestamp, 'aaa' AS P1, NULL AS P2, 'ttt' AS P5, 'zzz' AS P6 UNION
SELECT '20100809' AS ParameterTimestamp, NULL AS P1, 'bbb' AS P2, NULL AS P5, NULL AS P6
In the resulting dataset, the ParameterTimestamp contains all distinct values of column ParameterTimestamp from @params table.
The values of a row for P1...Pn columns should have same ParameterTimestamp in @params table. For example, the third row that has '20100808' ParameterTimestamp has value 'aaa' for P1 and 'ttt' for P5.
Thanks,
ioani
September 6, 2010 at 7:56 am
Herer's the CrossTab method I would use. It's describe in more details in the related link in my signature.
One thing to notice: Your sample data have multiple rows for P5 and 20100803. The code will display the largest value.
SELECT
ParameterTimestamp,
MAX(CASE WHEN ParameterID ='P1' THEN ParameterValue ELSE NULL END) AS P1,
MAX(CASE WHEN ParameterID ='P2' THEN ParameterValue ELSE NULL END) AS P2,
MAX(CASE WHEN ParameterID ='P5' THEN ParameterValue ELSE NULL END) AS P5,
MAX(CASE WHEN ParameterID ='P6' THEN ParameterValue ELSE NULL END) AS P6
FROM @params
GROUP BY ParameterTimestamp
September 6, 2010 at 8:00 am
trying doing it by using PIVOT operator. below is the example
DECLARE @params AS TABLE
(
ParameterID NVARCHAR(8) NOT NULL,
ParameterValue SQL_VARIANT NOT NULL,
ParameterTimestamp DATETIME NOT NULL
)
INSERT INTO @params( ParameterID, ParameterValue, ParameterTimestamp)
SELECT 'P1', '111', '20100801' UNION
SELECT 'P1', '222', '20100803' UNION
SELECT 'P1', 'aaa', '20100808' UNION
SELECT 'P2', 'bbb', '20100809' UNION
SELECT 'P5', 'ccc', '20100803' UNION
SELECT 'P5', 'sss', '20100803' UNION
SELECT 'P5', 'ttt', '20100808' UNION
SELECT 'P6', 'zzz', '20100808'
SELECTParameterTimestamp, [P1] AS P1, [P2] AS P2, [P5] AS P5, [P6] AS P6
FROM(
SELECTParameterID, ParameterValue, ParameterTimestamp
FROM@params
) AS p
PIVOT
(
MIN(ParameterValue)
FOR ParameterID IN([P1], [P2], [P5], [P6])
) AS pvt
ORDER BY ParameterTimestamp
Abhijit - http://abhijitmore.wordpress.com
September 7, 2010 at 12:20 am
Sorry, I have a little error in may sample data. P5 should have only one '20100803' ParameterTimestamp value. The ParameterID should have distinct ParameterTimestamp (the ParameterID and ParamterTimestamp form the primary key).
The big problem is that I do not know how many rows are in @params table. The values from ParameterID column are not known before. I think that the query should be built somehow dynamic.
September 7, 2010 at 2:51 am
ioani (9/7/2010)
Sorry, I have a little error in may sample data. P5 should have only one '20100803' ParameterTimestamp value. The ParameterID should have distinct ParameterTimestamp (the ParameterID and ParamterTimestamp form the primary key).The big problem is that I do not know how many rows are in @params table. The values from ParameterID column are not known before. I think that the query should be built somehow dynamic.
Why did I see this coming? 😀
Have a look at the DynamicCrossTab article referenced in my signature to change the CrossTab I provided earlier into a "flexible Pivot". Give it a try and post back where you get stuck (including the code you have so far).
September 7, 2010 at 6:43 am
This is alos for a Dynamic PIVOT
Failing to plan is Planning to fail
September 7, 2010 at 7:01 am
Thank you,
I managed to solve the problem.
September 7, 2010 at 10:15 am
ioani (9/7/2010)
Thank you,I managed to solve the problem.
Would you mind sharing your solution so others might benefit from it?
September 7, 2010 at 11:54 am
Abhijit More (9/6/2010)
trying doing it by using PIVOT operator. below is the example
DECLARE @params AS TABLE
(
ParameterID NVARCHAR(8) NOT NULL,
ParameterValue SQL_VARIANT NOT NULL,
ParameterTimestamp DATETIME NOT NULL
)
INSERT INTO @params( ParameterID, ParameterValue, ParameterTimestamp)
SELECT 'P1', '111', '20100801' UNION
SELECT 'P1', '222', '20100803' UNION
SELECT 'P1', 'aaa', '20100808' UNION
SELECT 'P2', 'bbb', '20100809' UNION
SELECT 'P5', 'ccc', '20100803' UNION
SELECT 'P5', 'sss', '20100803' UNION
SELECT 'P5', 'ttt', '20100808' UNION
SELECT 'P6', 'zzz', '20100808'
SELECTParameterTimestamp, [P1] AS P1, [P2] AS P2, [P5] AS P5, [P6] AS P6
FROM(
SELECTParameterID, ParameterValue, ParameterTimestamp
FROM@params
) AS p
PIVOT
(
MIN(ParameterValue)
FOR ParameterID IN([P1], [P2], [P5], [P6])
) AS pvt
ORDER BY ParameterTimestamp
Read the Cross Tab article in Lutz's signature to find out why you might not want to use Pivot. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2010 at 12:21 pm
Quite a while ago I thought using PIVOT would be, well... "cool".
After I figured how the syntax needs to be I was surprised that it didn't provide any performance gain at all nor any other benefit in terms of readability, maintainability a.s.o.
As of today, we have not a single PIVOT command in production code.
I wish Microsoft would have spent the effort/money developing a command to easily support running totals (for instance)...
Edit: UNPIVOT is a totally different story though....
September 7, 2010 at 11:57 pm
The solution I found for the problem:
-- Input data--
IF OBJECT_ID('#Params') IS NOT NULL
DROP TABLE #Params
CREATE TABLE #Params
(
ParameterID NVARCHAR(8) NOT NULL,
ParameterValue SQL_VARIANT NOT NULL,
ParameterTimestamp DATETIME NOT NULL
)
GO
INSERT INTO #Params( ParameterID, ParameterValue, ParameterTimestamp)
SELECT 'P1', '111', '20100801' UNION
SELECT 'P1', '222', '20100803' UNION
SELECT 'P1', 'aaa', '20100808' UNION
SELECT 'P2', 'bbb', '20100809' UNION
SELECT 'P5', 'ccc', '20100801' UNION
SELECT 'P5', 'sss', '20100803' UNION
SELECT 'P5', 'ttt', '20100808' UNION
SELECT 'P6', 'zzz', '20100808'
-- The solution --
DECLARE @Sql1 AS NVARCHAR(MAX);
DECLARE @Sql2 AS NVARCHAR(MAX);
SELECT @Sql1 = COALESCE(@Sql1, '') + 'MAX(CASE WHEN ParameterID = ' + QUOTENAME(ParameterID, '''') + ' THEN ParameterValue ELSE NULL END) AS ' + ParameterID + ',' + CHAR(10) + CHAR(9)
FROM #Params
GROUP BY ParameterID
SET @Sql2 = 'SELECT ParameterTimestamp, ' + CHAR(10) + CHAR(9) + STUFF(@Sql1, LEN(@Sql1) - 2, 2, '') + CHAR(10) + 'FROM #Params' + CHAR(10) + 'GROUP BY ParameterTimestamp'
PRINT @Sql2
EXECUTE(@Sql2);
September 8, 2010 at 5:22 am
LutzM (9/7/2010)
Quite a while ago I thought using PIVOT would be, well... "cool".After I figured how the syntax needs to be I was surprised that it didn't provide any performance gain at all nor any other benefit in terms of readability, maintainability a.s.o.
As of today, we have not a single PIVOT command in production code.
I wish Microsoft would have spent the effort/money developing a command to easily support running totals (for instance)...
Edit: UNPIVOT is a totally different story though....
I don't know if you've ever seen the PIVOT command in Access but MS sure could have done it that way. It's absolutely awesome and is, in fact, what I believe most people would have wanted as a PIVOT command. Dynamic PIVOTs would have been a cake walk, too because {drum roll please}, they wouldn't have been necessary anymore.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2010 at 5:39 am
ioani (9/7/2010)
The solution I found for the problem:
-- Input data--
IF OBJECT_ID('#Params') IS NOT NULL
DROP TABLE #Params
CREATE TABLE #Params
(
ParameterID NVARCHAR(8) NOT NULL,
ParameterValue SQL_VARIANT NOT NULL,
ParameterTimestamp DATETIME NOT NULL
)
GO
INSERT INTO #Params( ParameterID, ParameterValue, ParameterTimestamp)
SELECT 'P1', '111', '20100801' UNION
SELECT 'P1', '222', '20100803' UNION
SELECT 'P1', 'aaa', '20100808' UNION
SELECT 'P2', 'bbb', '20100809' UNION
SELECT 'P5', 'ccc', '20100801' UNION
SELECT 'P5', 'sss', '20100803' UNION
SELECT 'P5', 'ttt', '20100808' UNION
SELECT 'P6', 'zzz', '20100808'
-- The solution --
DECLARE @Sql1 AS NVARCHAR(MAX);
DECLARE @Sql2 AS NVARCHAR(MAX);
SELECT @Sql1 = COALESCE(@Sql1, '') + 'MAX(CASE WHEN ParameterID = ' + QUOTENAME(ParameterID, '''') + ' THEN ParameterValue ELSE NULL END) AS ' + ParameterID + ',' + CHAR(10) + CHAR(9)
FROM #Params
GROUP BY ParameterID
SET @Sql2 = 'SELECT ParameterTimestamp, ' + CHAR(10) + CHAR(9) + STUFF(@Sql1, LEN(@Sql1) - 2, 2, '') + CHAR(10) + 'FROM #Params' + CHAR(10) + 'GROUP BY ParameterTimestamp'
PRINT @Sql2
EXECUTE(@Sql2);
Very nice and well done. Thank you for taking the time to post your solution. 🙂
You can do a bit of simplification using the power built into the COALESCE to get rid of STUFF and an extra tab. Use of the quotes instead of NULL also gets rid of the NULL Aggregate message which can be interpreted by some GUI code as an error.
-- The solution --
DECLARE @Sql1 AS NVARCHAR(MAX);
DECLARE @Sql2 AS NVARCHAR(MAX);
SELECT @Sql1 = COALESCE(@Sql1 + ',' + CHAR(10) , '') + CHAR(9) + 'MAX(CASE WHEN ParameterID = ' + QUOTENAME(ParameterID, '''') + ' THEN ParameterValue ELSE '''' END) AS ' + ParameterID
FROM #Params
GROUP BY ParameterID
SET @Sql2 = 'SELECT ParameterTimestamp, ' + CHAR(10) + @SQL1 + CHAR(10) + 'FROM #Params' + CHAR(10) + 'GROUP BY ParameterTimestamp'
PRINT @Sql2
EXECUTE(@Sql2);
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply