CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
Spacko
Spacko

Reputation: 397

SQL Server Merge 2 rows into 1

Is it possible to merge 2 rows into a single row in SSRS 2008? Each part 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   |
+-----------+-------+-------+

Thank you

Upvotes: 12

Views: 35093

Answers (3)

Fabio
Fabio

Reputation: 32445

If you know your site numbers/names will not change dynamically then 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

By grouping we eliminated a NULL values...

Here link with SQL Fiddle example

Upvotes: 16

praveen
praveen

Reputation: 12271

In SSRS to you need to use MATRIX report to convert rows into columns without using PIVOT operator

Suppose you have a table SSRSPivot 

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 below 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 datasource and in the dataset write the query 

  Select PartNumber ,Cost,Site from SSRSPivot

enter image description here

Drag a matrix from the toolbox onto the SSRS designer.For Rows select PartNumber .For columns select Site and for the data select Sum(Cost)

enter image description here

When you do the above step ,you will get the row and column details like the one below

enter image description here

Final Result will look like

enter image description here

Upvotes: 2

Taryn
Taryn

Reputation: 247670

This type of data transformation is known as a PIVOT. Starting in SQL Server 2005, there is a function that can transpose the data into columns for you.

If you have a known number of Site values that you want to turn into columns, then you can hard-code 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, then you will need to use dynamic SQL to generate the column list 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 |

Upvotes: 1

Related Questions