SQL query to transfer values from rows to columns for subselect groups

Here's the problem:

T开发者_JS百科he dataset is:

Col1        Col2  
BK.01.04    A0103  
BK.01.04    A0306  
BK.01.04    A0309  
BK.01.04    A0403  
BK.02.01    A1403  
BK.02.02    A1403  
BK.02.03    A0403  
BK.02.03    A0703  
BK.02.04    A0103  
BK.02.04    A0306  
BK.02.04    A0309  
BK.02.04    A0403 

The required result is:

Col1        Col2    Col3    Col4    Col5  
BK.01.04    A0103   A0306   A0309   A0403  
BK.02.01    A1403  
BK.02.02    A1403  
BK.02.04    A0103   A0306   A0309   A0403  

Any ideas on how to do this in MS Access/Plain SQL? Any help is greatly appreciated :)

---

Join the table back to itself:

select
    t1.col1,
    t1.col2,
    t2.col2 as col3,
    t3.col2 as col4,
    t4.col2 as col5
from mytable t1
left join mytable t2 on t2.col1 = t1.col1 and t2.col2 > t1.col2
left join mytable t3 on t3.col1 = t1.col1 and t3.col2 > t2.col2 and t2.col2 is not null
left join mytable t4 on t4.col1 = t1.col1 and t4.col2 > t3.col2 and t3.col2 is not null
order by 1;

Using left join means you'll get blanks when there aren't matching rows in the joined table. All that crap in the join conditions is to make sure there's a different and ascending value in each column

---

Not quite exactly what you wanted, but I think you'll find it challenging to have the leftmost column populated with the value you want...

A pivot table using ANSI-SQL:

CREATE TABLE DataSet ( Col1 varchar(20), Col2 varchar(20))

INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.01.04','A0103')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.01.04','A0306')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.01.04','A0309')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.01.04','A0403')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.01','A1403')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.02','A1403')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.03','A0403')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.03','A0703')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.04','A0103')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.04','A0306')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.04','A0309')
INSERT INTO DataSet ( Col1, Col2 ) VALUES ( 'BK.02.04','A0403')

SELECT Col1,
MAX(CASE Col2 WHEN 'A0103' THEN 'A0103' ELSE '' END) Col2,
MAX(CASE Col2 WHEN 'A0306' THEN 'A0306' ELSE '' END) Col3,
MAX(CASE Col2 WHEN 'A0309' THEN 'A0309' ELSE '' END) Col4,
MAX(CASE Col2 WHEN 'A0403' THEN 'A0403' ELSE '' END) Col5,
MAX(CASE Col2 WHEN 'A1403' THEN 'A1403' ELSE '' END) Col6
FROM DataSet 
GROUP BY DataSet.Col1

Results Are:

Col1                 Col2  Col3  Col4  Col5  Col6
-------------------- ----- ----- ----- ----- -----
BK.01.04             A0103 A0306 A0309 A0403 
BK.02.01                                     A1403
BK.02.02                                     A1403
BK.02.03                               A0403 
BK.02.04             A0103 A0306 A0309 A0403 

(5 row(s) affected)

If you're using MS-Access, you can accomplish the same thing by using pivot-tables:

http://office.microsoft.com/en-us/access-help/designing-your-first-pivottable-pivotchart-views-in-access-HA001034580.aspx