Possible Duplicate:
Split Multiple Columns into Multiple Rows
My table look like this
accounting | research | sales | operations
------------------------------------------
3 | 5 | 6 | 0
It is necessary to receive a resultant set with two columns and four lines
dname | cnt
-----------------------
accounting | 3
research | 5
sales | 6
operations | 0
Use the UNPIVOT table operator like so:
DECLARE @t table (accounting int, research int, sales int, operations int);
INSERT INTO @t VALUES(3, 5, 6, 0);
SELECT dname, cnt
FROM
(
SELECT accounting, research, sales, operations
FROM @t
) t
UNPIVOT
(
cnt FOR dname IN (accounting, research, sales, operations )
) u
Here is the standard sql query for doing this, for RDBMS that doesn't support UNPIVOT table operator:
SELECT dname,
CASE dname
WHEN 'accounting' THEN accounting
WHEN 'research' THEN research
WHEN 'sales' THEN sales
WHEN 'operations' THEN operations
END AS cnt
FROM @t
CROSS JOIN
(
SELECT 'accounting' dname
UNION ALL SELECT 'research'
UNION ALL SELECT 'sales'
UNION ALL SELECT 'operations'
) t2
WHERE dname IS NOT NULL
Not all RDBMS have the UNPIVOT function, so another way to do this if you do not have the UNPIVOT operator is with UNION ALL:
select 'accounting' dname, IsNull(accounting, 0) cnt
from yourtable
union all
select 'research' dname, IsNull(research, 0) cnt
from yourtable
union all
select 'sales' dname, IsNull(sales, 0) cnt
from yourtable
union all
select 'operations' dname, IsNull(operations, 0) cnt
from yourtable
