0

I have the following table data:

name | serviceName | contractNo | materialNo
--------------------------------------------
item1  managed foo   123456       A6Ax33444
item1  managed bar   534454       A6A3222x7

And this is the result I need:

name | serviceName1 | contractNo1 | materialNo1 | serviceName2 | contractNo2 | materialNo2
item1  managed foo    123456        A6Ax33444     managed bar    534454        A6A3222x7

This example only contains two service-names but there may be zero to about 10 different services.

I tried several things using pivoting and dynamic SQL queries like this SQL Server: Examples of PIVOTing String data or Combine multiple rows into multiple columns dynamically in SQL Server but can't get it to work.

The real data is queried using multiple joins, 'name' comes from one table, 'serviceName', 'contractNo' and 'materialNo' are joined from another one and there are a lot more columns there, but the content is the same.

Thanks for help!

Community
  • 1
  • 1
Jan
  • 39
  • 5
  • There shouldn't be a Problem in doing it via dynamic SQL: First declare a variable which takes the total number of a Count(distinct ) and thenrepeat from 0 to Var and concat a string... but the question is: How do you want to handle the table later on? As you mentioned, it is possible to have 0 to 10 columns... so maybe now you create a table with the max of 10 cols... tomorrow you receive a 11th row - and now? And furthermore: even if you don't store it in a table but Display it in some Kind of reporting only: what will be the size of the line? Why not displaying it in some form of table/tree – Tyron78 Jul 05 '16 at 13:06
  • @Rene: Thanks for your comment. The whole stuff is used in a stored procedure which is called by EF6 and then displayed in an ASPxGrid (devexpress). – Jan Jul 05 '16 at 13:10

1 Answers1

0

Here I give the solution for Two rows only,. Try this.

If it works for you, you have to try for more.

select d.name
    ,max(case when Rn = 1 then cast(ServiceName as NVARCHAR) end) ServiceName1
    ,max(case when Rn = 1 then cast(ContractNo as NVARCHAR) end) ContractNo1
    ,max(case when Rn = 1 then cast(MeterialNo as NVARCHAR) end) MeterialNo1
    ,max(case when Rn = 2 then cast(ServiceName as NVARCHAR) end) ServiceName2
    ,max(case when Rn = 2 then cast(ContractNo as NVARCHAR) end) ContractNo2
    ,max(case when Rn = 2 then cast(MeterialNo as NVARCHAR) end) MeterialNo2
from (
select *
,ROW_NUMBER() over(PARTITION BY name ORDER BY(select null)) Rn
from your_Table
)d
group by d.name
DineshDB
  • 5,998
  • 7
  • 33
  • 49