0

I have a table name called table1 with one column called ID like this
ID
1
2
3
4
5
6
7
8
9
10

now i want the out put like this out put

1 2 3 4 5 6 7 8 9 10

that means 1 coumn with different values becomes 1 row
the coumns names can be any thing like id1 id2 id3 ,.....
i.e
id1 id2 id3 id4........ID10
1 2 3 4 ........10

is it possible to get like this ? if so how ?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Munna
  • 3
  • 2

5 Answers5

1

You are trying to transpose a resultset. Here is good information on how to do this in SQL, using PIVOT. Simple way to transpose columns and rows in Sql?

Another option is to copy the data in excel and transpose it there (it has a Paste Special option built in).

Community
  • 1
  • 1
Adrian Nasui
  • 1,054
  • 9
  • 10
1

A combination of Dynamic SQL and the PIVOT keyword will do what you want.

This question has been asked many times before:

Just do a google search for "t-sql dynamic pivot"

Community
  • 1
  • 1
Dan
  • 10,480
  • 23
  • 49
0

You can use PIVOT funcftion like this:

select * from table1

pivot
(
  MAX(ID) for ID in ([1],[2],[3],[4],[5],[6])
) piv

Instead of "MAX(ID)" you can insert the vaules that you want to have in your rows.

Nightmaresux
  • 538
  • 3
  • 12
0

try the following:

declare @tab1 table (id int)

insert into @tab1 select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10

select * from @tab1

select [1] as id1, [2] as id2, [3] as id3, [4] as id4, [5] as id5, [6] as id6, [7] as id7, [8] as id8, [9] as id9, [10] as id10
from
(
select id from @tab1
) t
pivot
(
max(id) for id in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
)pvt

You can you dynamic SQL to generalize your query.

Thanks.

sacse
  • 3,634
  • 2
  • 15
  • 24
-1

SQL queries always result in before-known columns. This is not given for your task. So you cannot solve this with SQL alone.

But usually you would use the SQL in a program and a program is written in a programming language. You would use that language (be it PHP, Java, C# or whatever) to format the output according to your wishes.

EDIT: Okay, I got a downvote. Was my answer too honest? Too straight forward? :-) Or just not elaborate enough? In case of the latter, here is some code to demonstrate what to do. This is a very simple task. Yes, there are ways to solve this in a more complicated way, but why would you do this?

$query = $db->query("select id from table1 order by id");
for($i=1; $row = $query->fetch_object(); ++$i) 
{
  $header = $header."<td>ID ".$i."</td>";
  $data = $data."<td>".$row->id."</td>";
}
echo "<tr>".$header."</tr>";
echo "<tr>".$data."</tr>";

This code may have flaws. I havent worked with mysqli so far.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Using dynamic sql, you can have queries that return before-unknown columns. [Here](http://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot) is an example of how to use dynamic SQL with the `PIVOT` keyword to do exactly this. – Dan Nov 28 '14 at 08:26
  • What? Why he cant use a PIVOT function, it will do exactly what he wants or not? – Nightmaresux Nov 28 '14 at 08:27
  • @Dan: Yes, you can use a language to build the SQL. T-SQL as a mix of SQL and programming language offers that (as does PL/SQL for Oracle, or Java and the like for that matter). But you cannot solve this with SQL alone. As this request is tagged mysqli, it is likely that PHP must be able to deal with the result list, so it might be preferable to use PHP to select and display the data as desired. – Thorsten Kettner Nov 28 '14 at 08:51
  • @Nightmaresux: PIVOT also demands before-known result rows. You must know the IDs when writing the query. – Thorsten Kettner Nov 28 '14 at 08:53
  • You don't need programming language to write dynamic SQL in T-SQL... take a look at [sp_executesql](http://msdn.microsoft.com/en-us/library/ms188001.aspx). So this problem can actually be solved entirely using T-SQL. Also, why wouldn't PHP be able to handle a recordset with an unknown number of columns? – Dan Nov 28 '14 at 09:20
  • @Dan: I am saying T-SQL is not SQL. It is a mix of SQL plus a procedural programming language. You can write dynamic SQL with T-SQL. You cannot however use SQL alone to solve the problem, you need the programming language. As to PHP: I don't know enough about its db features. Can you execute a T-SQL script as you would execute a query? Or must you write a T-SQL function to call? I don't know. All I can say is: This is a very, very simple select with a very, very simple PHP code. `select id from table1` and a loop. Can it be much simpler? – Thorsten Kettner Nov 28 '14 at 10:04