I'm very new to SQL, and I'm not sure how to accomplish the following task: I have a set of data in a table, and I need to convert the data into one of two values, then adding up the totals per row in a pair of summary columns. See below:
tbl1:
row P1 P2 P3
--- --- --- ---
1 a b c
2 a a a
3 b c b
4 a b b
5 NULL NULL NULL
with a conversion table as:
PNum PType
---- -----
a car
b truck
c car
The desired end result:
row P1 P2 P3 numCars numTrucks
--- --- --- --- ------- ---------
1 a b c 2 1
2 a a a 3 0
3 b c b 1 2
4 a b b 1 2
5 NULL NULL NULL NULL NULL
Code to get the test tables:
DECLARE @tbl1 TABLE(P1 VARCHAR,P2 VARCHAR,P3 VARCHAR)
INSERT INTO @tbl1
SELECT 'a','b','c' UNION ALL
SELECT 'a','a','a' UNION ALL
SELECT 'b','c','b' UNION ALL
SELECT 'a','b','b'
DECLARE @tbl2 TABLE(PNum VARCHAR,PType NVARCHAR(100))
INSERT INTO @tbl2
SELECT 'a','car' UNION ALL
SELECT 'b','truck' UNION ALL
SELECT 'c','car'
I've been looking into PIVOT but I'm having a hard time seeing how to implement it, (especially with the conversion piece). I thought about trying to first insert a set of columns just to convert the values (e.g. "P1_converted"), then trying to summarize them but my actual table has 10 of these type columns and I didn't want to add an additionial 10 columns to the table if there is a simpler way. Any thoughts? Thanks!
I'm using SQL Server 2008.
You can use the following which will unpivot the data to get the total count for the cars and trucks.
You can use the UNPIVOT function but since you are using SQL Server 2008, you can use CROSS APPLY and VALUES to convert your P1, P2, and P3 from columns into rows. The code to convert the data is:
select *
from tbl1
cross apply
(
values ('p1', p1), ('p2', p2), ('p3', p3)
) c(col, value)
See SQL Fiddle with Demo. This converts the data to the format:
| P1 | P2 | P3 | COL | VALUE |
------------------------------
| a | b | c | p1 | a |
| a | b | c | p2 | b |
| a | b | c | p3 | c |
| a | a | a | p1 | a |
Once the data is in the rows, then you can easily join to the conversion table to get the count for each row:
select rn, p1, p2, p3,
sum(case when t.ptype = 'car' then 1 else 0 end) numCars,
sum(case when t.ptype = 'truck' then 1 else 0 end) numTrucks
from
(
select p1, p2, p3,
row_number() over(order by p1, p2, p3) rn
from tbl1
) d
cross apply (values ('p1', p1), ('p2', p2), ('p3', p3)) c(col, value)
inner join tbl2 t
on c.value = t.pnum
group by rn, p1, p2, p3
See SQL Fiddle with Demo. This gives the result:
| RN | P1 | P2 | P3 | NUMCARS | NUMTRUCKS |
-------------------------------------------
| 1 | a | a | a | 3 | 0 |
| 2 | a | b | b | 1 | 2 |
| 3 | a | b | c | 2 | 1 |
| 4 | b | c | b | 1 | 2 |