Rows to columns

[poizn]

Hi all

I have a bit of a strange query I need to do and need some help.

Lets say I have a table that looks like

tab1
ipkTab1
ifkCompany
sCode
sNum

Lets says this table is populated like so

ipkTab1 , ifkCompany , sCode , sNum
1 , 1 , NT , 01
2 , 1 , OT , 02
3 , 1 , PUB , 03
4 , 1 , SUN , 04
5 2 NT 01
6 2 TA 02
7 2 SA 04

Now lets say I have another table, like so

tab2
ifkCompany
N01
N02
N03
N04

I need to get all of the code values, from the first table, into the second table, where all code values for one company must be in one row, like so

ifkCompany , N01 , N02 , N03 , N04
1 , NT , OT , PUB , SUN
2 , NT , TA , , SA

I created some ridiculous way of doing this, but its not very reliable. The only other way I can think of doing it, is manually... In other words, creating a query to get the code values for a company where sNum = 01, then another query where sNum = 02 etc...

Is there a better way to do this?

Thanks in advance

[poizn]

Found a solution.
Don't know if there is a better way, but here goes

INSERT INTO tab2 (ifkCompany , N01 , N02 , N03 , N04)
SELECT ifkCompany
     , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '01' THEN sCode ELSE '' END)) AS N01
     , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '02' THEN sCode ELSE '' END)) AS N02
     , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '03' THEN sCode ELSE '' END)) AS N02
     , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '04' THEN sCode ELSE '' END)) AS N02
FROM tab1
WHERE ifkCompany = X
GROUP BY ifkCompany;

Anyone got anything better?

[r937]

SELECT ifkCompany
     , MAX(CASE WHEN sNum = '01' THEN sCode END) AS N01
     , MAX(CASE WHEN sNum = '02' THEN sCode END) AS N02
     , MAX(CASE WHEN sNum = '03' THEN sCode END) AS N03
     , MAX(CASE WHEN sNum = '04' THEN sCode END) AS N04
FROM tab1
GROUP BY ifkCompany;

[poizn]

Wow, I don't know why that works, but its does

Thanks