Click here to Skip to main content
15,357,967 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.
Gerry Schmitz 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
   
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