How to select all column values as one row?

Question

This is my table values:

-----------------------------
   id | rackname | routeid |
-----------------------------
  1   | rack1    |   1    
  2   | rack2    |   1
  3   | rack3    |   2
  4   | rack4    |   1
  5   | rack5    |   2
  6   | rack6    |   1
  .
  .
  .

I need to select only one row form this table that need to select all the values based on routeid using SQL Server 2014.

I need to generate a row like this..

---------------------------------------------------
id  | rackname1 | rackname2 | rackname3 | rackname4....
---------------------------------------------------
 1  | rack1     | rack2     | rack4     | rack6    ...

How to generate this result? Thanks in advance.

What variety of SQL are you using? And will there only ever be 6 racks? — Tim Biegeleisen, Dec 09 '15 at 06:12
Please format your question property. You can check guidelines how to ask question and is it SQL- Server question or for oracle or for mysql? — Nikunj Soni, Dec 09 '15 at 06:12
If SQL Server: http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Adrian Lynch, Dec 09 '15 at 06:16
its sql server and now its having 15 rackds in future it may change — John babu19, Dec 09 '15 at 06:33
thanks adrian. but i need to generate only one row based on route id. — John babu19, Dec 09 '15 at 06:39
i tried like this but i can't understand this. select * from ( select rackname, id from racks ) src pivot( sum(id) for rackname in ([1], [2], [3], [4]) ) racks; Now m studying pivot.. — John babu19, Dec 09 '15 at 07:59
Try this - https://stackoverflow.com/questions/13730484/select-multiple-rows-from-single-column-into-single-row — Kate, Sep 06 '17 at 10:41

score 1 · Answer 1 · answered Dec 09 '15 at 06:56

1

You can do with PL/SQL

Declare table type variable (At the end you need to pivot on this variable so make sure for structure).

Now push desire data on this table.

Then pivot this table.

answered Dec 09 '15 at 06:56

Bhavik Jani

180
7

SQL Server has no PL/SQL – Dec 09 '15 at 08:43
PL/SQL means you cannot not do with simple one line sql query. – Bhavik Jani Dec 10 '15 at 06:56

How to select all column values as one row?

1 Answers1