I have table which contain the following information. 
I want to run a query and show only one line. something like this
I try to use a case statement, However i maybe have other employee who work in different state, and i do not want to list 50 state since most of employee may only work 2-3 state, but in different state. Any help will appreciate. Thanks
Since you want to pivot on multiple columns, my suggestion would be to first look at unpivoting the State, StateWages and StateTax columns then apply the PIVOT function.
You didn't specify what version of SQL Server you are using but you can use either UNPIVOT to CROSS APPLY to convert these multiple columns into rows. The syntax will be similar to:
select PRCo, Employee,
col = c.col + cast(seq as varchar(50)),
c.value
from
(
select PRCo, Employee, State, StateWages, StateTax,
row_number() over(partition by PRCo, Employee
order by state) seq
from yourtable
) d
cross apply
(
select 'State', State union all
select 'StateWages', cast(StateWages as varchar(10)) union all
select 'StateTax', cast(StateTax as varchar(10))
) c (col, value);
See SQL Fiddle with Demo. You'll notice that before I implemented the CROSS APPLY, I used a subquery with row_number() to create a unique value for each row per employee. The reason for doing this is so you can return the multiple state columns, etc. Once you have done this you can use PIVOT:
select PRCo, Employee,
State1, StateWages1, StateTax1,
State2, StateWages2, StateTax2
from
(
select PRCo, Employee,
col = c.col + cast(seq as varchar(50)),
c.value
from
(
select PRCo, Employee, State, StateWages, StateTax,
row_number() over(partition by PRCo, Employee
order by state) seq
from yourtable
) d
cross apply
(
select 'State', State union all
select 'StateWages', cast(StateWages as varchar(10)) union all
select 'StateTax', cast(StateTax as varchar(10))
) c (col, value)
) src
pivot
(
max(value)
for col in (State1, StateWages1, StateTax1,
State2, StateWages2, StateTax2)
) p;
See SQL Fiddle with Demo. The above version works great if you have a limited number of value but it sounds like you need a dynamic solution. Using the code above you can easily convert it to dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10)))
from
(
select row_number()
over(partition by PRCo, Employee
order by state) seq
from yourtable
) d
cross apply
(
select 'State', 1 union all
select 'StateWages', 2 union all
select 'StateTax', 3
) c (col, so)
group by col, so, seq
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT PRCo, Employee, ' + @cols + N'
from
(
select PRCo, Employee,
col = c.col + cast(seq as varchar(50)),
c.value
from
(
select PRCo, Employee, State, StateWages, StateTax,
row_number() over(partition by PRCo, Employee
order by state) seq
from yourtable
) d
cross apply
(
select ''State'', State union all
select ''StateWages'', cast(StateWages as varchar(10)) union all
select ''StateTax'', cast(StateTax as varchar(10))
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + N')
) p '
exec sp_executesql @query;
See SQL Fiddle with Demo. Both versions will give a result:
| PRCO | EMPLOYEE | STATE1 | STATEWAGES1 | STATETAX1 | STATE2 | STATEWAGES2 | STATETAX2 |
|------|----------|--------|-------------|-----------|--------|-------------|-----------|
| 1 | 304 | CA | 20162.03 | 804.42 | IN | 20162.03 | 665.90 |
