A sample with UNPIVOT and CTE


Here is a sample for UNPIVOT function along with CTE to find the occurance from an Excel imported table.

There is a solution by using SQL Server 2005 UNPIVOT function and CTE.

CREATE TABLE [dbo].[tableData](

[a] [int]

NULL,

[b] [int]

NULL,

[c] [int] NULL,

[d] [int]

NULL,[id] [int] IDENTITY(1,1) NOT NULL

)

END

GO

SET IDENTITY_INSERT [dbo].[tableData] ON

INSERT

[dbo].[tableData] ([a], [b], [c], [d], [id]) VALUES (0, 1, 2, 3, 1)

INSERT

[dbo].[tableData] ([a], [b], [c], [d], [id]) VALUES (1, 1, 2, 2, 2)

INSERT

[dbo].[tableData] ([a], [b], [c], [d], [id]) VALUES (2, 2, 0, 3, 3)

SET

IDENTITY_INSERT [dbo].[tableData] OFF

–Here is the script you can modify to hold more columns

with

mycte

AS

(

SELECT id, col, val FROM tableData

UNPIVOT

(

val for col IN ([a], [b], [c], [d]) ) –you can add more columns here

AS

unpvt

)

SELECT

 id, SUM(CASE WHEN val=1 THEN 1 ELSE 0 END) as [no of 1’s],

SUM

(CASE WHEN val=2 THEN 1 ELSE 0 END) as [no of 2’s],

SUM

(CASE WHEN val=3 THEN 1 ELSE 0 END) as [no of 3’s]

from

mycte

group

by id

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s