SQLTeam.com | Weblogs | Forums

How to convert row value as column in query


#1

Hi

I am having table 'tblcalculate' with field names

1)Hardness ---numeric,

2)tower ---int

I am having values

Hardness towers

1.2 1

2.2 2

3.2 1

5.2 3

6.2 2

Now I want to get as output


1 2 3 (towers) column head


1.2 2.2 5.2 (Hardness)

3.2 6.2 (Hardness)

Please help me to get output as above

Thanks and Regards

Ram


#2

Rather than post a screenshot, if you post the DDL to create the tables and sample data, that would make it much easier for someone to copy that, and then create a query against that sample data. What I mean is something like this:

CREATE TABLE #T (Hardness FLOAT, Towers INT);
INSERT INTO #T VALUES
(1.2, 1),
(2.2, 2),
(3.2, 1),
(5.2, 3),
(6.2, 2);

Now that I have the sample data, I can write a query against it like this:

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY Towers ORDER BY Hardness) AS RN
	FROM
		#T
) 
SELECT 
	*
FROM
	cte
PIVOT
	(MAX(Hardness) FOR  Towers IN ([1],[2],[3]))P