Pivot table query in SQL Server

Question

I have table as shown below. I need to get the values corresponding to maximum date timestamp for each distinct element and convert the rows to columns using SQL Server 2005 or higher. The column name values (i.e. columns A1, A2, A3 are dynamic and can change depending on data available)

Base table (from a resulting query)

Id  Name    Value   DateTimeStamp
--------------------------------------
1   A1  5   Nov 14 2011 03:12:12:947PM
1   A1  6   Nov 15 2011 03:12:12:947PM
1   A2  7   Nov 14 2011 03:12:12:947PM
1   A2  8   Nov 15 2011 03:12:12:947PM
1   A3  9   Nov 14 2011 03:12:12:947PM
2   A3  10  Nov 15 2011 03:12:12:947PM
2   A1  1   Nov 14 2011 03:12:12:947PM
2   A1  2   Nov 15 2011 03:12:12:947PM
2   A2  3   Nov 14 2011 03:12:12:947PM
2   A2  4   Nov 15 2011 03:12:12:947PM
2   A3  5   Nov 14 2011 03:12:12:947PM
2   A3  6   Nov 15 2011 03:12:12:947PM

Desired output:

Id  A1  A2  A3
---------------
1   6   8   10
2   2   4   6

Does name column have limited number of values? Also, what is the datatype for DateTimeStamp column? — shree.pat18, Dec 02 '15 at 05:30
Possible duplicate of [T-SQL dynamic pivot]. (http://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot) Note that you would need to join back the pivot resutls to table to get the value corresponding to the max datetime. — shree.pat18, Dec 02 '15 at 05:33
Do not want to use dynamic query. Any solutions available using static query. — TrustyCoder, Dec 02 '15 at 05:37
You have number of values that you do not know in advance. How do you expect static query to work? — shree.pat18, Dec 02 '15 at 05:37
If you use a static query you need to pre-allow for all possible columns — Nick.Mc, Dec 02 '15 at 05:38

score 0 · Answer 1 · answered Dec 02 '15 at 05:41

0

You can do this using dynamic crosstab:

SQL Fiddle

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql =
'SELECT
    Id' + CHAR(10)

SELECT @sql = @sql +
    ', MAX(CASE WHEN Name = ''' + Name +  ''' THEN Value END) AS ' + QUOTENAME(Name) + CHAR(10)
FROM (SELECT DISTINCT Name FROM tbl) t
ORDER BY Name

SELECT @sql = @sql +
'FROM (
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY Id, Name ORDER BY DateTimeStamp DESC)
    FROM tbl
) t
WHERE Rn = 1
GROUP BY Id
ORDER BY Id'

EXEC sp_executesql @sql

answered Dec 02 '15 at 05:41

Felix Pamittan

31,544
7
41
67

Any possible solution without the use of dynamic query. the problem is i need to join the results to another subquery. Your effort is very much appreciated. Thanks. – TrustyCoder Dec 02 '15 at 05:59
Since you have unknown number of `Name`s, you can't do this without using dynamic SQL. – Felix Pamittan Dec 02 '15 at 06:02
You can try inserting the result to a temporary table. – Felix Pamittan Dec 02 '15 at 06:03

Pivot table query in SQL Server

1 Answers1