CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
roman
roman

Reputation: 33

How to transpose data

I'm trying to return the data in this table in one row like this:

Incident  | Location | Order_Num |Item       |Shift     | Shift 2       |Shift 3 |Team_Member 1 |Team_Member 2  |Team_Member 3 |Team_Mem_ID 1|  Team_Mem_ID 2|  Team_Mem_ID 3|

M21-R17-1D| North_Am |948427     |T16_Term_GN|  2       |Weekend        |    | Chris Smith    |Sergei Skripal  | Megan Cohen   |248         |   1184         |   1662        |

Is this something that could be done with PIVOT or UNPIVOT? My environment is MS SQL 2008.

Code to create the table / data: 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sample_Table](
    [Incident] [varchar](20) NULL,
    [Location] [varchar](20) NULL,
    [Order_Num] [varchar](10) NULL,
    [Item] [varchar](30) NULL,
    [Shift] [varchar](20) NULL,
    [Team_Member] [varchar](101) NULL,
    [Team_Mem_ID] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Sample_Table] ([Incident], [Location], [Order_Num], [Item], [Shift], [Team_Member], [Team_Mem_ID]) VALUES (N'M21-R17-1D', N'North_Am', N'948427', N'T16_Term_GN', N'2', N'Chris Smith', N'248')
INSERT [dbo].[Sample_Table] ([Incident], [Location], [Order_Num], [Item], [Shift], [Team_Member], [Team_Mem_ID]) VALUES (N'M21-R17-1D', N'North_Am', N'948427', N'T16_Term_GN', N'2', N'Sergei Skripal', N'1184')
INSERT [dbo].[Sample_Table] ([Incident], [Location], [Order_Num], [Item], [Shift], [Team_Member], [Team_Mem_ID]) VALUES (N'M21-R17-1D', N'North_Am', N'948427', N'T16_Term_GN', N'2', N'Megan Cohen', N'1662')
INSERT [dbo].[Sample_Table] ([Incident], [Location], [Order_Num], [Item], [Shift], [Team_Member], [Team_Mem_ID]) VALUES (N'M21-R17-1D', N'North_Am', N'948427', N'T16_Term_GN', N'Weekend', N'Chris Smith', N'248')
INSERT [dbo].[Sample_Table] ([Incident], [Location], [Order_Num], [Item], [Shift], [Team_Member], [Team_Mem_ID]) VALUES (N'M21-R17-1D', N'North_Am', N'948427', N'T16_Term_GN', N'Weekend', N'Sergei Skripal', N'1184')
INSERT [dbo].[Sample_Table] ([Incident], [Location], [Order_Num], [Item], [Shift], [Team_Member], [Team_Mem_ID]) VALUES (N'M21-R17-1D', N'North_Am', N'948427', N'T16_Term_GN', N'Weekend', N'Megan Cohen', N'1662')

Upvotes: 0

Views: 64

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

You can accomplish this using PIVOT but I would not recommend it. I suggest using APPLY because it allows you to pivot or unpivot multiple columns much easier.

Here's my solution:

SELECT 
  Incident        = MAX(Incident),
  [Location]      = MAX([Location]),
  Order_Num       = MAX(Order_Num),
  [Shift]         = MAX(CASE [shift] WHEN '2' THEN [shift] END),
  [Shift2]        = MAX(CASE [shift] WHEN 'weekend' THEN [shift] END),
  [Shift3]        = MAX(CASE [shift] WHEN '3' THEN [shift] END),
  [Team_Member 1] = MAX(CASE rnk WHEN 1 THEN Team_Member END),
  [Team_Member 2] = MAX(CASE rnk WHEN 2 THEN Team_Member END),
  [Team_Member 3] = MAX(CASE rnk WHEN 3 THEN Team_Member END),
  [Team_Mem_ID 1] = MAX(CASE rnk WHEN 1 THEN Team_Mem_ID END),
  [Team_Mem_ID 2] = MAX(CASE rnk WHEN 2 THEN Team_Mem_ID END),
  [Team_Mem_ID 3] = MAX(CASE rnk WHEN 3 THEN Team_Mem_ID END)
FROM 
(
  SELECT t.*, rnk = DENSE_RANK() OVER (ORDER BY CAST(team_mem_id AS int))
  FROM dbo.sample_table t
) t
GROUP BY Incident, [Location], Order_Num;

Returns:

Incident      Location   Order_Num  Shift   Shift2   Shift3  Team_Member 1   Team_Member 2   Team_Member 3  Team_Mem_ID 1  Team_Mem_ID 2  Team_Mem_ID 3
------------- ---------- ---------- ------- -------- ------- --------------- --------------- -------------- -------------- -------------- -------------
M21-R17-1D    North_Am   948427     2       Weekend          Chris Smith     Sergei Skripal  Megan Cohen    248            1184           1662

Note that I am assuming you need Team_mem_id 1,2, & 3 are sorted by Team_mem_id. If it's really an int then you should update that column to be an int.

Upvotes: 1

Related Questions