Convert columnar data into rows

I have a table of data that needs to be converted to rows but with a way to identify for each row the column the row value came from.

Here's an example table:

Employee

FED

MEDI

SOCSEC

123

$100

$25

$70

234

$242

$54

$90

345

$75

$10

$55

Here's the format the data needs to be in after it's converted.

Employee

CodeType

Code

Amount

123

T

FED

$100

123

T

MEDI

$25

123

T

SOCSEC

$70

234

T

FED

$242

234

T

MEDI

$54

234

T

SOCSEC

$90

345

T

FED

$75

345

T

MEDI

$10

345

T

SOCSEC

$55

The CodeType will always be T.

This seems similar to some of the transpose-type samples but different enough (for me) to ask about.

Thank you for your help.

DECLARE @.Employee TABLE
(Employee int,
FED decimal(9,2),
MEDI decimal(9,2),
SOCSEC decimal(9,2))
INSERT @.Employee
SELECT 123, 100, 25, 70 UNION ALL
SELECT 234, 242, 54, 90 UNION ALL
SELECT 345, 75, 10, 55

SELECT Employee, 'T' AS CodeType, 'FED' AS Code, FED AS Amount
FROM @.Employee
UNION ALL
SELECT Employee, 'T' AS CodeType, 'MEDI' AS Code, MEDI AS Amount
FROM @.Employee
UNION ALL
SELECT Employee, 'T' AS CodeType, 'SOCSEC' AS Code, SOCSEC AS Amount
FROM @.Employee
ORDER BY Employee, Code

|||

You can do below:

select t.Employee

, 'T' as CodeType

, case c.n

when 1 then 'FED'

when 2 then 'MEDI'

when 3 then 'SOCSEC'

end as Code

, case c.n

when 1 then t.FED

when 2 then t.MEDI

when 3 then t.SOCSEC

end as Amount

from t

cross join (select 1 union all select 2 union all select 3) as c(n)

|||Thank you for your help. It is somewhat similar to the approach I took. The values were for illustration and will be much greater in quantity than the sample data I provided.

What I wound up doing was a series of select statements with a union all between them.|||Thank you for your answer. I'd like to use this but I'm a little lost on the case c.n portion and how that would be changed to fit the actual table and field names.|||

It's just creating a result set that is a single column named n with three rows holding the values 1, 2, and 3. Then you join to that result set with a cross join meaning you get three copies of each of your original rows matched with 1, 2, and 3 respectively. Then it uses that to determine which of the amounts and code types to use. In the example I gave you before it would be like this

DECLARE @.Counter int
SET @.Counter = 1
DECLARE @.Employee TABLE
(Employee int,
FED decimal(9,2),
MEDI decimal(9,2),
SOCSEC decimal(9,2))
INSERT @.Employee
SELECT 123, 100, 25, 70 UNION ALL
SELECT 234, 242, 54, 90 UNION ALL
SELECT 345, 75, 10, 55

select t.Employee
, 'T' as CodeType
, case c.n
when 1 then 'FED'
when 2 then 'MEDI'
when 3 then 'SOCSEC'
end as Code
, case c.n
when 1 then t.FED
when 2 then t.MEDI
when 3 then t.SOCSEC
end as Amount
from @.Employee t
cross join (select 1 union all select 2 union all select 3) as c(n)

Note that the as c(n) in the query is just assigning the column name that you use in the join, the last line of the query is exactly equivalent to

cross join (select 1 as n union all select 2 union all select 3) as c