CODEWITHSUNDEEP
sqlsql-servert-sql
ZeExplorer
ZeExplorer

Reputation: 553

Collapse/Consolidate Columns in TSQL query

I have a table which looks like this:

enter image description here

How would I select from this table such that the multiple subcode columns transform into rows ? Not sure if this is PIVOT problem. Please advise.

Desired Sample output:

enter image description here

Attached is SQL with data insert scripts for your reference. 

    USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubCodeReport](
    [ S-ID] [varchar](50) NULL,
    [AGE] [varchar](50) NULL,
    [SchoolCode] [varchar](50) NULL,
    [SubCode] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SubCodeReport3]    Script Date: 9/8/2015 6:05:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubCodeReport3](
    [ S-ID] [varchar](50) NULL,
    [AGE] [varchar](50) NULL,
    [SchoolCode] [varchar](50) NULL,
    [SubCode1] [varchar](50) NULL,
    [SubCode2] [varchar](50) NULL,
    [SubCode3] [varchar](50) NULL,
    [SubCode4] [varchar](50) NULL,
    [SubCode5] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'25', N'23', N'KEN-009', N'ENG')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'26', N'21', N'DLK-009', N'ENG')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'27', N'25', N'DLK-006', N'MAT')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'27', N'25', N'DLK-006', N'ENG')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'27', N'25', N'DLK-006', N'STAT')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'28', N'21', N'HLI-005', N'ENG')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'29', N'22', N'NUI-002', N'ENG')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'29', N'22', N'NUI-002', N'MAT')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'30', N'22', N'INN-009', N'ENG')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'30', N'22', N'INN-009', N'MAT')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'30', N'22', N'INN-009', N'ZOO')
INSERT [dbo].[SubCodeReport] ([ S-ID], [AGE], [SchoolCode], [SubCode]) VALUES (N'30', N'22', N'INN-009', N'GEO')
INSERT [dbo].[SubCodeReport3] ([ S-ID], [AGE], [SchoolCode], [SubCode1], [SubCode2], [SubCode3], [SubCode4], [SubCode5]) VALUES (N'25', N'23', N'KEN-009', N'ENG', N'', N'', N'', N'')
INSERT [dbo].[SubCodeReport3] ([ S-ID], [AGE], [SchoolCode], [SubCode1], [SubCode2], [SubCode3], [SubCode4], [SubCode5]) VALUES (N'26', N'21', N'DLK-009', N'ENG', N'', N'', N'', N'')
INSERT [dbo].[SubCodeReport3] ([ S-ID], [AGE], [SchoolCode], [SubCode1], [SubCode2], [SubCode3], [SubCode4], [SubCode5]) VALUES (N'27', N'25', N'DLK-006', N'MAT', N'ENG', N'STAT', N'', N'')
INSERT [dbo].[SubCodeReport3] ([ S-ID], [AGE], [SchoolCode], [SubCode1], [SubCode2], [SubCode3], [SubCode4], [SubCode5]) VALUES (N'28', N'21', N'HLI-005', N'ENG', N'', N'', N'', N'')
INSERT [dbo].[SubCodeReport3] ([ S-ID], [AGE], [SchoolCode], [SubCode1], [SubCode2], [SubCode3], [SubCode4], [SubCode5]) VALUES (N'29', N'22', N'NUI-002', N'ENG', N'MAT', N'', N'', N'')
INSERT [dbo].[SubCodeReport3] ([ S-ID], [AGE], [SchoolCode], [SubCode1], [SubCode2], [SubCode3], [SubCode4], [SubCode5]) VALUES (N'30', N'22', N'INN-009', N'ENG', N'MAT', N'ZOO', N'GEO', N'')

Upvotes: 1

Views: 912

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use unpivot to get the desired result.

Fiddle with sample data from the question

select [ S-ID], age, schoolcode, u.subcode
from subcodereport3
unpivot 
(subcode 
for x in (subcode1,subcode2,subcode3,subcode4,subcode5) ) u
where u.subcode <> ' '

Upvotes: 3

Related Questions