SQL Server Merge 2 rows into 1

Question

Can 2 lines be combined into one line in SSRS 2008? Each piece will have a record for each site.

+---------------+-------+-------+ |Part Number |Cost |Site | +---------------+-------+-------+ |1 |2.4 |Site 1 | |1 |68.8 |Site 2 | +---------------+-------+-------+ 

Desired Result

 +-----------+-------+-------+ |Part Number|Site 1 |Site 2 | +-----------+-------+-------+ | 1 |2.4 |68.8 | +-----------+-------+-------+ 

thanks

Answer 1

If you know that the numbers / names of your site will not change dynamically, you can use CASE WHEN : s

 SELECT PartNumber, MAX(CASE WHEN Site=1 THEN Cost ELSE NULL END) AS Site1_Cost, MAX(CASE WHEN Site=2 THEN Cost ELSE NULL END) AS Site2_Cost FROM Parts GROUP BY PartNumber 

For groups, we excluded NULL values ​​...

Here with an example SQL Fiddle

Answer 2

This type of data conversion is known as PIVOT . Starting with SQL Server 2005, there is a feature that can wrap data in columns for you.

If you have a known number of Site values ​​that you want to include in the columns, you can program the query:

 select part_number, [Site 1], [Site 2] from ( select part_number, cost, site from yourtable ) src pivot ( sum(cost) for site in ([Site 1], [Site 2]) ) piv; 

See SQL Fiddle with Demo

But if you have an unknown number of values, you will need to use dynamic SQL to create a list of columns to be used in the query:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(site) from yourtable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT part_number,' + @cols + ' from ( select part_number, cost, site from yourtable ) x pivot ( sum(cost) for site in (' + @cols + ') ) p ' execute(@query) 

See SQL Fiddle with Demo . Both will give the result:

 | PART_NUMBER | SITE 1 | SITE 2 | --------------------------------- | 1 | 2.4 | 68.8 | 

Answer 3

In SSRS, you need to use a MATRIX report to convert rows to columns without using the PIVOT operator

Suppose you have an SSRSPivot table

 Create table SSRSPivot (PartNumber int ,Cost decimal(18,2),Site varchar(max)) Insert into SSRSPivot values (1,2.4,'Site 1'), (1,68.8,'Site 2' ) 

The data is in the following format

 +---------------+-------+-------+ |PartNumber |Cost |Site | +---------------+-------+-------+ |1 |2.4 |Site 1 | |1 |68.8 |Site 2 | +---------------+-------+-------+ 

Create a new blank report and name it as PIVOT . Create a data source and write a query in a dataset

  Select PartNumber ,Cost,Site from SSRSPivot 

Drag a MATRIX from the toolbar to the SSRS constructor. For Rows select PartNumber . For columns, select Site and to select Sum(Cost) data

When you do the above step, you will get row and column data as below

The final result will look like