-6

I have a table of data like this:

RecID     Name      Value
1         Color     Red
2         Size      Small
3         Weight    20lbs
4         Shape     Square

I need a query that returns the rows as columns, like this:

Color     Size     Weight     Shape
Red       Small    20lbs      Square

What would the SQL query look like to do this? I cannot hard code any values into the query, it just needs to read the Name and Value pairs and re-orient them horizontally.

Blaze
  • 1,863
  • 7
  • 23
  • 40
  • The answer that you guys keep marking as a duplicate of does not have the answer I need. – Blaze Apr 09 '14 at 19:37
  • Correct answer here: http://stackoverflow.com/questions/23060311/sql-query-unknown-rows-into-columns – Blaze Apr 14 '14 at 13:56

1 Answers1

0

Test Data

DECLARE @Table TABLE(RecID INT,Name VARCHAR(20),Value VARCHAR(20))
INSERT INTO @Table VALUES
(1,'Color' ,'Red'),
(2,'Size'  ,'Small'),
(3,'Weight','20lbs'),
(4,'Shape' ,'Square')

Query

SELECT *
FROM
(SELECT Name,Value 
FROM @Table) T
PIVOT ( MAX(Value)
        FOR Name
        IN ([Color],[Size],[Weight],[Shape])
        )P

Result Set

╔═══════╦═══════╦════════╦════════╗
║ Color ║ Size  ║ Weight ║ Shape  ║
╠═══════╬═══════╬════════╬════════╣
║ Red   ║ Small ║ 20lbs  ║ Square ║
╚═══════╩═══════╩════════╩════════╝
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • I can't hard code the [Color], {Size] as you have it in the IN grouping. Is there a way to query the name value pairs and re-orient them horizontally without knowing what they are? – Blaze Apr 09 '14 at 19:41
  • To pivot dynamically unknown number of column please see this answer [`How to pivot unknown number of columns & no aggregate in SQL Server?`](http://stackoverflow.com/questions/22772481/how-to-pivot-unknown-number-of-columns-no-aggregate-in-sql-server/22773815#22773815), And if this answer has helped you please condiser accepting it as your answer thank you. – M.Ali Apr 09 '14 at 19:41
  • Not the best with SQL, I'll see what I come up with. Thanks! – Blaze Apr 09 '14 at 23:28