change column values to row

• rosarozina

  SSC-Addicted

  Points: 484

  More actions

  August 24, 2018 at 10:30 am

  #408307

  I want to change the values in the column to row based on their ID number.
  What I want to do is to filter out the ones with the same PhaseId that have the lowest date with specific ItemName first like this:

        
    
  PhaseId
    
  ItemName
    
  PercentIn 
    
  PercentOut 
    
  Date
   
   
    1         Hardware       1.5       1.5          8/24/2017
    1         Wood           1         1            3/21/2018
    1         Panel          1         1            8/15/2017
    1         Glass          1         1            1/18/2018
    2         Hardware       1         0            5/18/2018
    3         Glass          1         1            5/20/2017
    4         Panel          1         0            6/15/2017
  

  and then put them in a row instead of a column.
  I want something like this:

  PhaseId    ItemName    PercentIn    PercentOut      Date       ItemName2     PercentIn2    PercentOut2    Date2       ItemName3    PercentIn3    PercentOut3    Date3     ItemName4    PercentIn4     PercentOut4    Date4
  1                Hardware       1.5               1.5             8/24/217     Wood               1                        1             3/21/2018    Panel             1                      1                   8/15/17     Glass            1                         1                  1/18/18
  2                Hardware        1                  0               5/1/2017       Null                Null                    Null            Null              Null               Null                 Null                Null          Null              Null                      Null               Null
  3                Glass               1                  1               5/20/2017     Null               Null                     Null            Null              Null               Null                 Null                Null          Null             Null                       Null              Null
  4               Panel                1                   0              6/15/17          Null               Null                    Null            Null               Null              Null                 Null                 Null          Null             Null                      Null              Null
  

  What I used was the following code but didnt give me the desired result:
  
  SELECT PhaseID,T1.ItemName,PercentIn,PercentOut,Date
  FROM TestTable T1
  INNER JOIN
      (SELECT ItemName, MIN(Date) AS MINDATE FROM TestTable GROUP BY ItemName) T2
      ON T1.ItemName = T2.ItemName AND T1.Date = T2.MINDATE

  Here is the code for creating the table:
  
  CREATE TABLE [dbo].[TestTable] (
     [PhaseId] [int] NOT NULL,
       [ItemName] [nvarchar](50) null,
       [PercentIN] [float] null,
       [PercentOut] [float] null,
     [Date] [Date] NULL,

  )
  GO

  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE', 1.5, 1.5, '8/24/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE', 1,0, '')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE',0.6,0.6, '3/22/2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE', 1,1, '3/29/2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'WOOD', 1,1, '3/21/2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'PANEL', 1,1, '8/15/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'PANEL', 1.6,1.6, '4/5/2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1, 'GLASS', 1,0, '2/3/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'GLASS', 1,1, '1/18/2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (2,'HARDWARE', 1,0, '5/18/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (2,'HARDWARE', 1.6,1.6, '8/5/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (3,'GLASS', 1,0, '3/5/2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (3,'GLASS', 1,1, '5/20/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (3,'WOOD', 1,1, '7/8//2018')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (4,'PANEL', 1,0, '6/15/2017')
  INSERT [dbo].[TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (4,'PANEL', 1,1, '8/10/2017')

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  August 24, 2018 at 3:28 pm

  #2003290

  Your test data is not loadable.

• rosarozina

  SSC-Addicted

  Points: 484

  More actions

  August 24, 2018 at 4:14 pm

  #2003294

  It is working for me, can you try it again?

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  August 24, 2018 at 5:08 pm

  #2003299

  Msg 241, Level 16, State 1, Line 27
  Conversion failed when converting date and/or time from character string.

• femi.olaniyan

  SSC Enthusiast

  Points: 151

  More actions

  August 29, 2018 at 9:42 am

  #2003831

  If at most 4 items you can use this:

  if OBJECT_ID('tempDb.dbo.#TestTable') is not null drop table #TestTable;

  CREATE TABLE [dbo].[#TestTable] (
[PhaseId] [int] NOT NULL,
[ItemName] [nvarchar](50) null,
[PercentIN] [float] null,
[PercentOut] [float] null,
[Date] [Date] NULL,

  )
GO

  INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE', 1.5, 1.5, '8/24/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE', 1,0, '')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE',0.6,0.6, '3/22/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'HARDWARE', 1,1, '3/29/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'WOOD', 1,1, '3/21/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'PANEL', 1,1, '8/15/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'PANEL', 1.6,1.6, '4/5/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1, 'GLASS', 1,0, '2/3/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (1,'GLASS', 1,1, '1/18/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (2,'HARDWARE', 1,0, '5/18/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (2,'HARDWARE', 1.6,1.6, '8/5/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (3,'GLASS', 1,0, '3/5/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (3,'GLASS', 1,1, '5/20/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (3,'WOOD', 1,1, '7/8/2018')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (4,'PANEL', 1,0, '6/15/2017')
INSERT [dbo].[#TestTable] ([PhaseId], [ItemName], [PercentIN], [PercentOut], [Date]) VALUES (4,'PANEL', 1,1, '8/10/2017')

  go

  ; with cte as
(

  select
a.PhaseId
,a.ItemName
,a.PercentIN
,a.PercentOut
,a.[date]
,ROW_NUMBER() over(PARTITION by a.PhaseId order by [Date]) [rn]

  If you have more categr
from
(
select b.*, ROW_NUMBER() over(PARTITION by b.PhaseId, b.ItemName order by [date] ) [rn]
from #TestTable b
where b.Date > '1900-01-01'
) a
where
a.rn = 1
)

  select *
from
(

  select a.PhaseId
,b.x
, b.n + isnull(cast((case when a.rn > 1 then a.rn end) as varchar),'') [cols]

  from cte a
outer apply(select n,x from ( VALUES ('ItemName', cast(a.ItemName as varchar)),('PercentIn', cast(a.PercentIn as varchar)),('PercentOut', cast(a.PercentOut as varchar)),('Date', cast(a.[Date] as varchar))) b(n,x)) b
) c
pivot
(
max(x)
For cols in ( ItemName,  PercentIn,  PercentOut,  Date ,  ItemName2 ,  PercentIn2 , PercentOut2,  Date2 ,  ItemName3 , PercentIn3 , PercentOut3 , Date3 ,  ItemName4,  PercentIn4,  PercentOut4 , Date4)
) pvt;
  

• femi.olaniyan

  SSC Enthusiast

  Points: 151

  More actions

  August 29, 2018 at 9:43 am

  #2003832

  If there can be potentially more items you need to use a dynamic pivot:
  if OBJECT_ID('tempDb.dbo.#temp') is not null drop table #temp;

  create table #temp
(
  PhaseId int,
  category varchar(50),
  [value] varchar(50),
  [idx] varchar(2),
  [order] varchar(2)
)

  go

  ; with cte as
(

  select
a.PhaseId
,a.ItemName
,a.PercentIN
,a.PercentOut
,a.[date]
,ROW_NUMBER() over(PARTITION by a.PhaseId order by [Date]) [rn]

  from
(
select b.*, ROW_NUMBER() over(PARTITION by b.PhaseId, b.ItemName order by [date] ) [rn]
from #TestTable b
where b.Date > '1900-01-01'
) a
where
a.rn = 1
)

  ,temp(PhaseId,category,[value],[idx], [order])
as
(
select a.PhaseId
, b.n + isnull(cast((case when a.rn > 1 then a.rn end) as varchar),'') [cols]
,b.x
,a.rn
,b.z
from cte a
outer apply(select n,x,z from ( VALUES ('ItemName', cast(a.ItemName as varchar),'1'),('PercentIn', cast(a.PercentIn as varchar),'2'),('PercentOut', cast(a.PercentOut as varchar),'3'),('Date', cast(a.[Date] as varchar),'4')) b(n,x,z)) b
)

  insert into #temp
select * from temp;

  DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);

  SET @cols = STUFF((SELECT ',' + QUOTENAME(c.category)
    FROM #temp c
    group by c.category ,c.idx, c.[order]
    order by c.idx,c.[order]
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1,1,'');

  set @query = 'SELECT PhaseId, ' + @cols + ' from
    (
      select PhaseId
       , [value]
       , category
      from #temp
    ) x
    pivot
    (
      max([value])
      for category in (' + @cols + ')
    ) p; '

  execute(@query);

• rosarozina

  SSC-Addicted

  Points: 484

  More actions

  August 29, 2018 at 3:09 pm

  #2003910

  femi.olaniyan - Wednesday, August 29, 2018 9:43 AM

  If there can be potentially more items you need to use a dynamic pivot:
  if OBJECT_ID('tempDb.dbo.#temp') is not null drop table #temp;

  create table #temp
  (
    PhaseId int,
    category varchar(50),
    [value] varchar(50),
    [idx] varchar(2),
    [order] varchar(2)
  )

  go

  ; with cte as
  (

  select
  a.PhaseId
  ,a.ItemName
  ,a.PercentIN
  ,a.PercentOut
  ,a.[date]
  ,ROW_NUMBER() over(PARTITION by a.PhaseId order by [Date]) [rn]

  from
  (
  select b.*, ROW_NUMBER() over(PARTITION by b.PhaseId, b.ItemName order by [date] ) [rn]
  from #TestTable b
  where b.Date > '1900-01-01'
  ) a
  where
  a.rn = 1
  )

  ,temp(PhaseId,category,[value],[idx], [order])
  as
  (
  select a.PhaseId
  , b.n + isnull(cast((case when a.rn > 1 then a.rn end) as varchar),'') [cols]
  ,b.x
  ,a.rn
  ,b.z
  from cte a
  outer apply(select n,x,z from ( VALUES ('ItemName', cast(a.ItemName as varchar),'1'),('PercentIn', cast(a.PercentIn as varchar),'2'),('PercentOut', cast(a.PercentOut as varchar),'3'),('Date', cast(a.[Date] as varchar),'4')) b(n,x,z)) b
  )

  insert into #temp
  select * from temp;

  DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);

  SET @cols = STUFF((SELECT ',' + QUOTENAME(c.category)
      FROM #temp c
      group by c.category ,c.idx, c.[order]
      order by c.idx,c.[order]
      FOR XML PATH(''), TYPE
      ).value('.', 'NVARCHAR(MAX)')
     ,1,1,'');

  set @query = 'SELECT PhaseId, ' + @cols + ' from
      (
        select PhaseId
         , [value]
         , category
        from #temp
      ) x
      pivot
      (
        max([value])
        for category in (' + @cols + ')
      ) p; '

  execute(@query);

  Thank you so much. It works great!!