Transverse row to column

Question

Post reply

Transverse row to column

antontj

SSC-Addicted

Points: 428
More actions
March 29, 2005 at 2:38 am

#163086
hi,
can anyone help me how to transverse rows into columns in SQL.
for ex, I have a table with data like this :
Branch      Item        Amount
------       -----      -------
Branch1     ItemA          10
Branch1     ItemB          10
Branch2     ItemB          10
Transverse result:
Item       Branch1   Branch2
-----      -------    --------
ItemA           10             0
ItemB           10            10
thanks in advance,
```
 
```

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

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

You can do crosstabs with sql, especially if you have a fixed, stable column list:

SELECT Item

,sum(case when branch = 'Branch1' then Amount else 0 end) as Branch1

,sum(case when branch = 'Branch2' then Amount else 0 end) as Branch2

,sum(case when branch = 'Branch3' then Amount else 0 end) as Branch3

, etc

FROM MyTable

GROUP BY Item

There are a number of scripts that will do this dynamically if you can't determine ahead of time what the column set is. Try: http://www.sqlteam.com/item.asp?ItemID=2955

There's even a 3rd party product with a 30-day full eval period:

http://www.rac4sql.net

Hope one of these helps,

Scott Thornburg

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

We use RAC4SQL and whilst it does have it's limitations is fairly useful at this sort of thing

creliuc ciprian SSC Veteran Points: 248 More actions · Answer 3

This is a code sample I use to make a "Pivot" like table with data extracted from the "Presence" database in my company

This table gets further analyse and in base of its results the people gets payed

Notice the conditional "Case" statements

I hope you find it useful

USE EW

------------------------set first day of week - monday--------------------------

--******************************************************************************

set datefirst 1

--Begin Select------------------------------------------------------------------

--******************************************************************************

SELECT risul.mat,risul.data,dizio.orario,datename(weekday,risul.data) as MyWeekDay,

case when dizio.orario in ('04','54','63') then '2' else 1 end as Shift,

--Sum of "ORDI" values- --------------------------------------------------------

--we use the cast statement to transform the string hour type HHmm to minutes of type decimal

--******************************************************************************

sum(case when risul.caus='ORDI'then

(cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))

ELSE 0 END) AS ORDI,

--Sum of "ORDI" values with reevaluation for those who worked in the second shift

--******************************************************************************

sum(case when risul.caus='ORDI' then--if we have "ORDI"

(case when dizio.orario in ('54','63')then--If we have second shift orario

(case when (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))>=210 then --if presence is >=3,5 Hours

cast(((cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))*8/7) as integer)--reevaluate it

else (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) end)--if not ,leave it as it is

else

(cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))--if not second shift orario, leave it as it is

end)

ELSE 0 END) AS ORDI_REEV,

--Sum of Normali Isola (N001...N200=presence of the Direct type of worker)-----

--******************************************************************************

sum(case when (risul.caus like 'N%' and risul.caus not like 'NOSA') then

(cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))

ELSE 0 END) AS NORMALI,

--Sum of Normali Isola (N001...N200) with Reevaluation for the second shift----

--******************************************************************************

sum(case when (risul.caus like 'N%' and risul.caus not like 'NOSA') then--if we have Normali Isola,

(case when dizio.orario in ('04')then--If we have second shift orario

(case when (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))>=210 then --if presence is >= 3,5 ore

cast(((cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))*8/7) as integer)--reevaluate it (*8/7)

else (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) end)--if not, leave it as it is

else

(cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))--if not second shift , leave it as it is

end)

ELSE 0 END) AS NORMALI_REEV,

--Sum of not requested Saturday presence D&I (NOSA & STSA)----------------------

--******************************************************************************

sum(case when risul.caus in ('STSA','NOSA') then

(cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))

ELSE 0 END) AS OSN,

--Sum of requested Saturday presence for the "Indirect" type of worker (S150)---

--******************************************************************************

sum(case when risul.caus in ('S150') then

(cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))

ELSE 0 END) AS S150,

--Sum of the requested Saturday presence (ECCS) for the "Direct" type of worker-

--Conditions: if first shift presence is greater than 360 minutes then truncate it

--Conditions: if second shift presence is greater than 300 minutes then truncate it

--first shift orario:05 & 06 ; second shift orario:07 & 08

--******************************************************************************

sum(case when risul.caus='ECCS' then

(case dizio.orario

when '07' then (case when (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))>360 then 360 else (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1)as decimal)) end)

when '08' then (case when (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))>360 then 360 else (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1)as decimal)) end)

when '05' then (case when (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))>390 then 390 else (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1)as decimal)) end)

when '06' then (case when (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal))>390 then 390 else (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1)as decimal)) end)

else 0 END)

else 0 end) as ECCS,

--------------------------sum of other single Causale---------------------------

--******************************************************************************

SUM(CASE risul.caus WHEN 'FM ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS C100,

SUM(CASE risul.caus WHEN 'RFPL' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS C200,

SUM(CASE risul.caus WHEN 'RF+ ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS C300,

SUM(CASE risul.caus WHEN 'RF- ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS C400,

SUM(CASE risul.caus WHEN 'ASMS' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS AMS,

SUM(CASE risul.caus WHEN 'ANM ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS ANM,

SUM(CASE risul.caus WHEN 'ANMS' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS ANS,

SUM(CASE risul.caus WHEN 'ASM ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS ASM,

SUM(CASE risul.caus WHEN 'CONG' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS CNM,

SUM(CASE risul.caus WHEN 'COPI' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS COP,

SUM(CASE risul.caus WHEN 'FNP ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS FNP,

SUM(CASE risul.caus WHEN 'INCO' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS INC,

SUM(CASE risul.caus WHEN 'MD ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS MAL,

SUM(CASE risul.caus WHEN 'S075' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS OP1,

SUM(CASE risul.caus WHEN 'RMD ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS RMD,

SUM(CASE risul.caus WHEN 'SCHI' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS SCH,

SUM(CASE risul.caus WHEN 'SED ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS SED,

SUM(CASE risul.caus WHEN 'FE ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS FE,

SUM(CASE risul.caus WHEN 'PF ' THEN (cast((left(risul.ore,2)*60 + right(risul.ore,2)*1) as decimal)) ELSE 0 END) AS PF

FROM risul,dizio

--filter for month=mars and year=2005----------------------------

--***************************************************************

where risul.mat=dizio.mat and risul.data=dizio.data and datepart(year,risul.data)='2005' and datepart(month,risul.data)='03'

GROUP BY risul.mat,risul.data,dizio.orario

order by risul.mat,risul.data

GO