Column to Rows Sql help

Question

Post reply

Column to Rows Sql help

SQL_Kills

Hall of Fame

Points: 3154
More actions
May 31, 2016 at 5:56 am

#314154

Hi,
I have the below table called sktrans, I want the columns to appear as rows, I can do this by doing a union but if there another easier way?
Thanks
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sktrans](
[Batch] [varchar](6) NULL,
[Account] [varchar](8) NULL,
[Item] [varchar](10) NULL,
[Ref] [varchar](10) NULL,
[PageNo] [varchar](4) NULL,
[ItemNo1] [varchar](10) NULL,
[ItemNo2] [varchar](10) NULL,
[ItemNo3] [varchar](10) NULL,
[ItemNo4] [varchar](10) NULL,
[ItemNo5] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into sktrans values ('S001','XXX','SC001','Trans','001','OP/00001','OP/00002','OP/00003','OP/00004','OP/00005')
insert into sktrans values ('S001','XXX','SC001','Trans','002','OP/00006','OP/00007','OP/00008','OP/00009','OP/00010')
insert into sktrans values ('S001','XXX','SC001','Trans','003','','OP/00011','OP/00012','OP/00013','OP/00014')
insert into sktrans values ('S002','XSFDF','DH005','Retu','001','OP/00001','OP/00002',NULL,'OP/00008','OP/00012')
Select Batch, Account, Item, Ref, PageNo,ItemNo1 as ItemNo
From sktrans
Union
Select Batch, Account, Item, Ref, PageNo,ItemNo2
From sktrans
Union
Select Batch, Account, Item, Ref, PageNo,ItemNo3
From sktrans
Union
Select Batch, Account, Item, Ref, PageNo,ItemNo4
From sktrans
Union
Select Batch, Account, Item, Ref, PageNo,ItemNo5
From sktrans

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You can do the same using CROSS APPLY

SELECT S.Batch, S.Account, S.Item, S.Ref, S.PageNo, F.ItemNo

FROM sktrans AS S

CROSS APPLY (

SELECT S.ItemNo1 AS ItemNo UNION

SELECT S.ItemNo2 UNION

SELECT S.ItemNo3 UNION

SELECT S.ItemNo4 UNION

SELECT S.ItemNo5

) AS F

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Avoid using UNION when you're not trying to remove duplicates. Use UNION ALL to include all items and avoid unnecessary sorts.

I would use a table valued constructor instead.

SELECT S.Batch, S.Account, S.Item, S.Ref, S.PageNo, F.ItemNo

FROM sktrans AS S

CROSS APPLY ( VALUES

(S.ItemNo1),

(S.ItemNo2),

(S.ItemNo3),

(S.ItemNo4),

(S.ItemNo5)

) AS F(ItemNo);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

GSquared SSC Guru Points: 260824 More actions · Answer 3

Any reason to not use UNPIVOT for this?

SELECT

Batch,

Account,

Item,

Ref,

PageNo,

ItemNo

FROM

(SELECT

*

FROM

sktrans) AS S

UNPIVOT ( ItemNo FOR ItemCol IN (ItemNo1, ItemNo2, ItemNo3, ItemNo4, ItemNo5) ) AS UP;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

GSquared (5/31/2016)
Any reason to not use UNPIVOT for this?

Confusing syntax, in my opinion. But that's a personal preference.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2