Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have following Table
Line    Values
72      25.72
74      35.74
72      24.72
76      40.76
72      22.72
74      32.50
76      15.50
76      20.15

I want result in multi columns like

Line72  Line74  Line76
25.72    35.74   40.76
24.72    35.50   15.50        
22.72    32.50   20.15


What I have tried:

I have tried PIVOT and many other query but unable, pls favor
Posted
Updated 15-Jan-21 18:32pm
v2
Comments
CHill60 15-Jan-21 8:44am    
The "What I have tried:" section is for you to place the code you have tried, so that we can help you fix it. We don't write code on demand.
[no name] 15-Jan-21 8:51am    
Use CASE and AS.

1 solution

Firstly, you will never get your expected results from the sample data you provided because there is a value missing. I used the following test data
SQL
declare @t table (identifier varchar(10), line int, [values] decimal(15,2))
insert into @t(identifier, Line, [Values]) values
('A',72, 25.72),
('A',74, 35.74),
('B',72, 24.72),
('A',76, 40.76),
('C',72, 22.72),
('B',74, 32.50),
('B',76, 15.50),
('C',76, 20.15),
-- This data item is missing from your example ...
('C',74, 32.50)
Note I have added an "identifier" and given it values that match the rows in your expected results. More of that later.

Gerry is suggesting doing something like this
SQL
SELECT  
		[72] = case when line = 72 then [values] else 0 END,
		[74] = case when line = 74 then [values] else 0 END,
		[76] = case when line = 76 then [values] else 0 END
	FROM @t

But check out the results
72	74	76
25.72	0.00	0.00
0.00	35.74	0.00
24.72	0.00	0.00
0.00	0.00	40.76
22.72	0.00	0.00
0.00	32.50	0.00
0.00	0.00	15.50
0.00	0.00	20.15
0.00	32.50	0.00
You don't want all those zeroes, so you will have to put these results into a sub-query, CTE or temporary table and GROUP BY something.

You mention that you had tried "PIVOT". You probably got results
72	74	76
25.72	35.74	40.76
which might explain your "without aggregations" bit in your question. That is because there is nothing to identify which values belong on which line of your expected results.

You could try generating an identifier e.g. using ROW_NUMBER()
SQL
SELECT 
	ROW_NUMBER() over (partition by [line] order by [line]) as rn, *
	FROM @t
or you could do what I did - include it in the original data - where you have complete control over it.
SQL
select [72],[74],[76]
from
(
	select identifier, Line, [Values]
	from @t
) a
PIVOT
(
	max([Values]) for Line in ([72],[74],[76])
) pvt
 
Share this answer
 
v2
Comments
Maciej Los 15-Jan-21 11:46am    
Great! A5!
CHill60 15-Jan-21 11:59am    
Thank you! Szczęśliwego Nowego Roku!
Maciej Los 15-Jan-21 13:35pm    
Thank you very much. And to You and your too :)
Member 7959046 16-Jan-21 5:56am    
But it is not a required solution, actually I want to generate a "Chart" for separate Lines with respective values, can't aggregate or empty values.
CHill60 16-Jan-21 9:36am    
My solution does not aggregate values nor are there any empty values. It produces your exact expected results

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900