August 26, 2016 at 1:24 am
Hi friends ,
i would like to transpose powerunit_natlang and transmission_natlang columns. please some one help me.
create table #urgent_2_11 (id int,cc_key varchar(50),technical_item_name_natlang varchar(50),modeltrim_name varchar(50),powerunit_natlang varchar(50),
transmission_natlang varchar(50),category_name_natlang varchar(50))
insert into #urgent_2_11 values(12017,'28c6483296abe739646b0b91b1d17fb4','Cilindrada','like','1,0 MPI 75 CV','Auto 5 vel.','Motorización y transmisión')
insert into #urgent_2_11 values(12022,'28c6483296abe739646b0b91b1d17fb4','Cilindrada','Like','1,0 MPI 75 CV','Manual 5 vel.','Motorización y transmisión')
insert into #urgent_2_11 values(12013,'28c6483296abe739646b0b91b1d17fb4','Largo','Like','1,0 MPI 75 CV','Manual 5 vel.','Dimensiones exteriores')
insert into #urgent_2_11 values(12014,'28c6483296abe739646b0b91b1d17fb4','Ancho','Like','1,0 MPI 75 CV','Manual 5 vel.','Dimensiones exteriores')
insert into #urgent_2_11 values(12015,'28c6483296abe739646b0b91b1d17fb4','Alto','Like','1,0 MPI 75 CV','Manual 5 vel.','Dimensiones exteriores')
insert into #urgent_2_11 values(12016,'28c6483296abe739646b0b91b1d17fb4','Distancia entre ejes','Like','1,0 MPI 75 CV','Manual 5 vel.','Dimensiones exteriores')
select * from #urgent_2_11
expected_output :
please find the below attachment :
August 26, 2016 at 7:40 am
You seem to have asked this question (or a version of it) several times.
You should be able to create a dynamic pivot for yourself now, unless you don't understand the solutions. If that's the case, ask questions, but don't expect people to do your job for you.
August 26, 2016 at 7:43 am
Thank you for doing an excellent job of setting up the problem for us. Good job. Looking at the problem all you really wanted to do was pivot the the values in transmission_natlang. That was simple enough. But there may be problems producing the column titles you want.
The code below will produce the results you want, but with several warnings:
(1) The original sort order is lost because there is no column or set of columns in your original data to control the sequence of the final output. If that sequence is critical, add a column to control the sequence, take the MIN() value of it in the summary query, and sort by the resulting column with an Order By.
(2) The case statements were created specifically for the two values presented in your sample data. This code will NOT automatically adapt itself to any other values.
(3) Adding the column names '1,0 MPI 75 CV' was also done dynamically. Again, the code will not automatically adapt itself to new values.
It appears that you want sql code to duplicate the functionality of a PIVOT in excel. SQL does not do have that capacity without coding more sophisticated dynamic SQL than I have time to create for you this morning. Perhaps someone else can pick this up and run with it. Good luck.
;with cte as (select technical_item_name_natlang, category_name_natlang
,case when transmission_natlang = 'Auto 5 vel.'
then modeltrim_name else null end as cv1
,case when transmission_natlang = 'Manual 5 vel.'
then modeltrim_name else null end as cv2
from #urgent_2_11)
select technical_item_name_natlang, category_name_natlang
, isnull(max(cv1),'-') as '1,0 MPI 75 CV'
, isnull(max(cv2),'-') as '1,0 MPI 75 CV'
from cte
group by technical_item_name_natlang, category_name_natlang
Edited to add: Sorry Luis, I forgot you were already working on this with him. If you are already well into dynamic SQL, then the above code is WAY behind the curve.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 30, 2016 at 8:31 am
dear The Dixie Flatline ,
thanks for your valuable time. but my full doubt is ..,
is it possible to transpose 2 rows at a same time one by one according to my excel sample. please find the excel sample below.
its like a tree format :
powerunit_natlang 1,0 MPI 75 CV1,0 MPI 75 CV
| | |
| | |
transmission_natlang Automatic Manual
then rest of datas shoul be come down.
August 30, 2016 at 11:45 am
If I understand you correctly, I regret to tell you it can't be done in the same fashion as your Excel spreadsheet without some complicated Dynamic SQL which would not be very flexible. My example was extremely forced and inflexible.
The design philosophy behind SQL is that it retrieves and returns rows of data, and that formatting such as you describe should be done in a client program, such as Excel.
If someone else has an elegant solution for your problem, I would be happy to learn from it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 30, 2016 at 12:06 pm
I had a good solution, but I won't post anything without seeing some real effort.
The problem seems to be coming from these 2 threads:
http://www.sqlservercentral.com/Forums/Topic1808920-3077-1.aspx
http://www.sqlservercentral.com/Forums/Topic1809713-3077-1.aspx
Once I see real effort and understanding of basic concepts, I'll post something.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply