Table Pivoting revisited
A lot of table pivoting scripts use cursors to reach the desired results, but SQL scripting was developed to be utilized in a data set environment rather than sequential step-thru scripting.
Normally, there are 3 types of Table Pivoting - 1) column explicit 2) column implicit and 3)single column. COLUMN EXPLICIT will place the row value in the exact column set for that data. COLUMN IMPLICIT will place the row value in the next available column. ONE COLUMN will place the row values into one wide column, usually separated by a space.
For the following script examples, lets assume there are accounts with multiple card types and our goal is to list the accounts once with all their card types accross.
sample data:
accounts card id
---------- ---- -------
1030047914 CK 1
1030047914 PA 2
1030047914 RD 3
1030047914 ST 4
1030047914 VA 5
1033403759 CK 6
1033403759 HI 7
1033403759 PA 8
1033403759 RD 9
1033403759 ST 10
1033403759 VA 11
1050098664 CK 12
1050098664 HI 13
1050098664 PA 14
1050098664 RD 15
1050098664 ST 16
1050098664 VA 17
Note - I created an ID column which will be needed later on,
and there are 7 types of cards, therefore, 7 columns.
------------------------------------------------------------
COLUMN EXPLICIT:
-- organize data into the right columns
select accounts,
case when card='CK' then card else ' ' end as card_ck,
case when card='HI' then card else ' ' end as card_hi,
case when card='PA' then card else ' ' end as card_pa,
case when card='PC' then card else ' ' end as card_pc,
case when card='RD' then card else ' ' end as card_rd,
case when card='ST' then card else ' ' end as card_st,
case when card='VA' then card else ' ' end as card_va
into tbl1_cards
from test_tbl
-- final pivoting step
select accounts,max(card_ck) as card_ck,max(card_hi) as card_hi,max(card_pa) as card_pa,
max(card_pc) as card_pc,max(card_rd) as card_rd,
max(card_st) as card_st,max(card_va) as card_va
into tbl2_cards
from tbl1_cards
group by accounts
order by accounts
-- results
accounts card_ck card_hi card_pa card_pc card_rd card_st card_va
---------- ------- ------- ------- ------- ------- ------- -------
1030047914 CK PA RD ST VA
1033403759 CK HI PA RD ST VA
1050098664 CK HI PA RD ST VA
------------------------------------------------------------
COLUMN IMPLICIT:
-- build table of sequential numbering by dupes
SELECT convert(char(1),(
SELECT COUNT(*)
FROM test_cards a
WHERE a.accounts = b.accounts and
a.id<=b.id
)
) AS rowno
, b.accounts
, b.card
into tbl1_cards
from test_tbl b
order by accounts
-- organize the data according to its sequential dupe #
select accounts,
case when rowno='1' then card else ' ' end as fld1,
case when rowno='2' then card else ' ' end as fld2,
case when rowno='3' then card else ' ' end as fld3,
case when rowno='4' then card else ' ' end as fld4,
case when rowno='5' then card else ' ' end as fld5,
case when rowno='6' then card else ' ' end as fld6,
case when rowno='7' then card else ' ' end as fld7
into tbl2_cards
from tbl1_cards
-- Final table, flatten or pivot the data
select accounts,max(fld1) as card1,max(fld2) as card2,max(fld3) as card3,
max(fld4) as card4,max(fld5) as card5,max(fld6) as card6,max(fld7) as card7
into tbl3_cards
from tbl2_cards
group by accounts
order by accounts
-- Results
accounts card1 card2 card3 card4 card5 card6 card7
-------- ----- ----- ----- ----- ----- ----- -----
1030047914 CK PA RD ST VA
1033403759 CK HI PA RD ST VA
1050098664 CK HI PA RD ST VA
------------------------------------------------------------
ONE COLUMN:
-- add the following to the end of the COLUMN IMPLICIT script
select accounts,fld1+' ' +fld2+' '+fld3+' '+fld4+' '+fld5+' '+fld6+' '+fld7 as cards
into tbl4_cards
from tbl3_cards
order by accounts
-- Results
accounts cards
-------- -----------------
1030047914 CK PA RD ST VA
1033403759 CK HI PA RD ST VA
1050098664 CK HI PA RD ST VA 
