How to flatten a table using SQL Server?

Question

I'm using SQL Server 2012. For a project I want to flatten a table, but i need some help. My table.

| ApplicationName   | Name        | Value                   | CreatedOn
| Contoso           | Description | An example website      | 04-04-2014
| Contoso           | Description | Nothing                 | 02-04-2014
| Contoso           | Keywords    | Contoso, About, Company | 04-04-2014
| Contoso           | Keywords    | Contoso, Company        | 02-04-2014

I want to get the last modification record from a Name by Application Name. The result i want.

| ApplicationName    | Description        | Keywords
| Contoso            | An example website | Contoso, About, Company

I don't like temp tables. Who knows how to do this?

Thanks a lot.

Jordy

http://stackoverflow.com/questions/24470/sql-server-pivot-examples http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Eric Hauenstein, Apr 04 '14 at 12:42
Do the values in `Name` change? will corresponding entries always have the same `CreatedOn` date? — Daniel E., Apr 04 '14 at 12:45
There can be values added to the Name column. The records will not be updated, a new record will be inserted by an update transaction. — Jordy, Apr 04 '14 at 12:52
Yikes. Way to use a RDBMS for non-relational data. What is Normalisation? http://en.wikipedia.org/wiki/Database_normalization — Jodrell, Apr 04 '14 at 13:49

dean · Accepted Answer · 2014-04-04T13:56:57.303

Here's the more complete solution:

declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(name) 
            FROM table -- your table here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

declare @q nvarchar(max)
set @q = '
select * 
from (
    select ApplicationName, name, Value
        from (
        select *, row_number() over (partition by ApplicationName, name order by CreatedOn desc) as rn
        from table -- your table here
        where appname = ''contoso''
    ) as x
    where rn = 1 
) as source
pivot (
    max(Value)
    for name in (' + @collist + ')
) as pvt
'

exec (@q)

pquest · Answer 2 · 2014-04-04T14:02:47.130

You are going to have to use a pivot table to do this. If the number of values for Name are unknown, you will also need to use some dynamic sql. 3 Articles you will probably find useful for this are:

Pivot Tables

SQL Server Cursors

Dynamic Sql

EDIT:

Here is a possible example

--Get the list of names

DECLARE @values varchar(MAX);

SELECT @values = COALESCE(@values, '') + '[' + Name + '],' FROM table;

SET @values = SUBSTRING(@values, 1, LEN(@values) - 1);

--build the sql string using these names

DECLARE @sql varchar(MAX) = 'SELECT applicationName, ' + @values + 'FROM (';
SET @sql = @sql + 'SELECT applicationName, Name, Value FROM tableName WHERE CreatedOn = (SELECT MAX(CreatedOn) FROM tableName WHERE ApplicationName = @appName) AND applicationName = @appName) AS toPivot';

SET @sql = @sql + 'PIVOT (';

SET @sql = @sql + 'MAX(Value) FOR Name IN (' + @values + ')) As p';

--run sql

DECLARE @ParamDefinition varchar(MAX) = '@appName varchar(MAX)';

DECLARE @selectedApp varchar(MAX) = 'put the app you want here';

EXECUTE sp_executesql @sql, @ParamDefinition, @appName = @selectedApp;

Note that I am assuming that the CreatedOn value will be the same for every Name you are interested in for an application.

Edit again:

The solution below mine has a really nice trick for working out the most recent date for each entry if they are different.

DISCLAIMER, since I did this in the answer window off the top of my head, it may not be 100% right, but it should get you pretty close.

Thank you for the links. I tried a lot of this stuff, but I can't fix it. — Jordy, Apr 04 '14 at 13:16

score -1 · Answer 3 · answered Apr 04 '14 at 14:33

Here is a basic way to do this. Replace the temp table with your table.

DECLARE @tmp TABLE (
   ApplicationName VARCHAR(25),
   [Name] VARCHAR(25),
   [Value] VARCHAR(100),
   CreatedOn Date )
INSERT INTO @tmp VALUES
   ('Contoso','Description','An example website','04-04-2014'),
   ('Contoso','Description','Nothing','02-04-2014'),
   ('Contoso','Keywords','Contoso, About, Company','04-04-2014'),
   ('Contoso','Keywords','Contoso, Company','02-04-2014')

SELECT a.ApplicationName,
  a.[Value] AS [Description],
  d.Value AS [Keywords]
FROM @tmp AS a
  -- filter the rows by max date
INNER JOIN (SELECT c.applicationName, MAX(c.CreatedOn) AS mCreated 
        FROM @tmp AS c 
        GROUP BY c.applicationName
        ) AS b ON a.ApplicationName = b.ApplicationName AND a.CreatedOn = b.mCreated 
AND a.Name = 'Description'
   -- go back and get the matching key words
INNER JOIN @tmp AS d ON a.ApplicationName = d.ApplicationName AND d.Name = 'Keywords' AND  
 d.CreatedOn = a.CreatedOn

How to flatten a table using SQL Server?

3 Answers3