SQLTeam.com | Weblogs | Forums

Selecting columns as rows


#1

If I have a table like the below
Booking ref cage box pallet carton
GE12344as 0 1 2 0
KD1231241 1 0 1 7

How can I create and SQL select statement that can return field columns into rows to produce something like...

Booking ref type qty
GE12344as box 1
GE12344as pallet 2
KD1231241 cage 1
KD1231241 pallet 1
KD1231241 carton 7


#2

The key here is UNPIVOT

;WITH cte AS
( SELECT 'GE12344as' AS [Booking Ref],0 AS Cage,1 AS box, 2 AS pallet, 0 AS carton
  UNION ALL
  SELECT 'KD1231241', 1 ,0 ,1 ,7
)


SELECT
    [Booking Ref]
    ,[Type]
    ,[Qty]

FROM
    cte
    UNPIVOT
    (
        [QTY] FOR [TYPE] IN ([Cage],[box],[pallet],[carton])
    )PV
WHERE
    [Qty] <> 0


Booking Ref Type       Qty
GE12344as   box        1
GE12344as   pallet     2
KD1231241   Cage       1
KD1231241   pallet     1
KD1231241   carton     7

#3

Thanks, first time using UNPIVOT for me