CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
Nayana
Nayana

Reputation: 1549

SQL Server 2008, pivot with no aggregate fuction

I have a database table that looks like this:

product||installed/removed||date
      A||                1||Jan 1, 2016
      A||                0||Mar 16, 2016
      A||                1||May 15, 2016
      B||                1||Feb 3, 2016
      C||                0||Nov 15, 2015
      D||                1||Dec 4, 2015

installed/removed is a flag which means 1 = installed, 0 =removed.

I want to make it look like this:

Product||           1|| 0
      A|| Jan 1 2016 || Mar 16, 2016
      A|| May 152016 || NULL
      B|| Feb 3 2016 || NULL
      C||       NULL || Nov 15, 2015
      D|| DEC 4 ,2015|| NUll

But using max/min won't let me display data this way...

Upvotes: 1

Views: 43

Answers (1)

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Using a PIVOT with ROW_NUMBER() , you can solve this,

DECLARE @TABLE  TABLE
(   product NVARCHAR(100)
    ,in_rem tinyint
    ,[date] date    
)

INSERT INTO @TABLE
VALUES ('A',1,'Jan 1, 2016'),('A',0,'Mar 16, 2016'),('A',1,'May 15, 2016'),
        ('B',1,'Feb 3, 2016'),('C',0,'Nov 15, 2015'),('D',1,'Dec 4, 2015')

SELECT  product,[1],[0]
FROM    (   SELECT  *
                    ,ROW_NUMBER() OVER(PARTITION BY in_rem ORDER BY product,[date]) AS [ROW_NO]
            FROM    @TABLE  
        )   AS  T
PIVOT (MIN([date]) FOR in_rem IN ([1],[0]))PVT
ORDER BY product

Upvotes: 2

Related Questions