How to build a query that SELECT rows into columns for SQL?

Question

How can I write a query in SQL Server that returns a column for each variable of each unique ID? Below is a simplified example of what I am trying to accomplish. Would you be able to point me in the right direction?

Table_Exists

ID    |    LOCATION    |    STATUS
__________________________________
001   |     top        |     3
001   |     mid        |     1
001   |     bot        |     4
002   |     top        |     2
002   |     mid        |     2
002   |     bot        |     1
003   |     top        |     1
003   |     mid        |     4
003   |     bot        |     2

Here is a simplified example of what I am trying, obviously without success. The code below returns an error message for incorrect syntax around '=':

SELECT  
  ID,
  LOCATION = top AS Top,
  LOCATION = mid AS Middle,
  LOCATION = bot AS Bottom
FROM 
  Table_Exists

I trying to build:

Table_Desired

ID | Top | Middle | Bottom 
__________________________
001|  3  |   1    |  4
002|  4  |   2    |  2 
003|  1  |   4    |  2

Have a look at the pivot command. stackoverflow.com/questions/18116020/… — domenicr
– domenicr, Commented Nov 25, 2015 at 19:18

Lukasz Szozda · Accepted Answer · 2015-11-27 12:22:09Z

3

You can use self join (as long as your LOCATION is NOT NULL and it contains all top/mid/bot values):

 SELECT t1.ID, t1.Status AS Top, t2.Status AS Middle, t3.Status AS Bottom
 FROM Table_Exists t1
 JOIN Table_Exists t2
   ON t1.ID = t2.ID
  AND t1.LOCATION = 'top'
  AND t2.Location = 'mid'
 JOIN Table_Exists t3
   ON t1.ID = t3.ID
  AND t1.LOCATION = 'top'
  AND t3.Location = 'bot';

LiveDemo

For more generic solution use PIVOT:

SELECT
     [ID]
    ,[Top]    = [top] 
    ,[Middle] = [mid]
    ,[Bottom] = [bot]
FROM #Table_Exists t
PIVOT (
   MAX(STATUS) FOR LOCATION IN ([top],[mid],[bot])
) AS Piv

LiveDemo2

edited Nov 27, 2015 at 12:22

answered Nov 25, 2015 at 19:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bret Shandro Over a year ago

Thanks @lad2025 This was very helpful

Bret Shandro Over a year ago

If any values for "Status" are 0/null the entire row for that "ID" will be excluded. If Table_Exists is missing data the PIVOT function can be used to return "ID" rows when "Status" is 0/null

Mihai · Accepted Answer · 2015-11-25 19:51:43Z

2

Use pivot function like this

SELECT
    ID,
    [top] AS [Top],
    [mid] AS Middle,
    [bot] AS Bottom
FROM(SELECT 
    ID, 
    LOCATION,
    STATUS
FROM 
    Table_Exists) AS SourceTable
PIVOT (AVG(STATUS) FOR LOCATION IN ([top],[mid],[bot])) AS PivotTable

edited Nov 25, 2015 at 19:51

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

answered Nov 25, 2015 at 19:43

G.Maltby

665 bronze badges

2 Comments

Bret Shandro Over a year ago

I am getting error message "Error converting data type nvarchar to int." Any thoughts? @Mihai

G.Maltby Over a year ago

Is your Status column a nvarchar? If so then CAST(STATUS, int) AS STATUS will let get the average

Collectives™ on Stack Overflow

How to build a query that SELECT rows into columns for SQL?

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related