1

I want to convert my output which is like 

BachNo|Release Qty 
----------
A     |10
A     |30
A     |40
B     |90
B     |30

I want to transpose this structure into

BatchNO | Qty Release1 | Qty Release2 | Qty Release3
----------------------------------------------------
A       |     10       |    30        |   40
B       |     90       |    30        |  Null

The number of rows generated in the 1st output will be dynamic, so the transposed output can have n number of columns

Dale K
  • 25,246
  • 15
  • 42
  • 71
Akhil R J
  • 184
  • 2
  • 14

2 Answers2

1

See below mention links

Efficiently convert rows to columns in sql server

http://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html

Dynamic Pivot in Sql Server

If still not found search on google there are many much examples on various technical sites https://www.google.co.in/?gws_rd=ssl#q=Convert+Rows+Into+Columns+SQL+Server

Community
  • 1
  • 1
Moumit
  • 8,314
  • 9
  • 55
  • 59
  • thanks for the links but none of it is helping me to get the column names dynamically , It just transposes the rows into column header , – Akhil R J Nov 10 '15 at 08:03
  • No problem .. first get schema inforamtion of query.. and using that information make `sql string` .. then using `sp_execSql` .. you can get your desired result ... If you using any programming language in backend then the process will less complex .. – Moumit Nov 10 '15 at 08:44
  • thanx it worked , And from the links i was able to understand the flow of Pivot – Akhil R J Nov 17 '15 at 12:08
0

For known number of columns its possible, but for dynamic number of columns am not very sure. Although you can use something like this and further split it later while processing.

SELECT BatchNo , STUFF(( SELECT  ','+ ReleaseQty FROM TableName a
WHERE b.BatchNo = a.BatchNo FOR XML PATH('')),1 ,1, '')  Members
FROM TableName b
GROUP BY BatchNo;

This should give you an output of something like :

BatchNo  | ReleaseQty
-------- | ------------------------
A        |  10,30,40
B        |  90,30
Maddy
  • 123
  • 4