February 15, 2014 at 11:46 am
Hi,
I have below table and within same query i need pivot and unpivot.
create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp
Now i need output in below format
name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30
Thanks
February 16, 2014 at 6:18 pm
Note that in your data setup 1-2-2013 translates to Jan 2 2013, but otherwise this should do it for you.
SELECT name1, name2, name3 -- , b.*
,[Jan-13]=MAX(CASE WHEN month1 = '2013-01-01' THEN emp END)
,[Feb-13]=MAX(CASE WHEN month1 = '2013-01-02' THEN emp END)
FROM #temp a
CROSS APPLY
(
VALUES (1, [month], emp1),(2, [month], emp2),(3, [month], emp3),(4, [month], emp4)
) b (n, [month1], emp)
GROUP BY name1, name2, name3, n;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2014 at 12:55 am
Dwain, nice way of doing it..!!
Is this a kind of cross-tab.??
March 11, 2014 at 4:45 am
Junglee_George (3/11/2014)
Dwain, nice way of doing it..!!Is this a kind of cross-tab.??
Actually, the CROSS APPLY VALUES is a bit of an UNPIVOT (see the first link in my signature articles). The rest is indeed a crosstab.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2014 at 5:48 am
you can try this
SELECT name1, name2, name3,empn
,[Jan-13]=MAX(CASE WHEN month1 = '2013-01-01' THEN emp END)
,[Feb-13]=MAX(CASE WHEN month1 = '2013-01-02' THEN emp END)
FROM #temp a
CROSS APPLY
(
VALUES ('emp1', [month], emp1),('emp2', [month], emp2),('emp3', [month], emp3),('emp4', [month], emp4)
) b (empn, [month1], emp)
GROUP BY name1, name2, name3, empn
it should be work , i had done small change above reply query....:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply