CODEWITHSUNDEEP
sqlsql-server
Mary
Mary

Reputation: 5

how to convert multiple rows to single row?

How to get sql with Query I have three tables in the form below

Table A

ID  | Title   |Count
--- |-------- |-----
1   |Mouse    | 50
2   |pen      | 60

Table B

ID  | CompName|
--- |---------|
1   |Comp1    |
2   | Comp2   | 
3   |Comp3    |

Table T

|---------------------|
|IDA    | IDB | CountT|
|-------|-----|-------|
|1      |   1 |  5    |     
|2      |   1 |  6    |     
|1      |   2 |  7    |     
+---------------------+

I want to make such a report

| object | Copm1 | Comp2 | Comp3 |Sum|remaining |
|--------|-------|-------|-------|---|--------- |
| Mouse  | 5     | 7     | 0     | 12| 38       |
| pen    | 6     | 0     | 0     | 6 | 54       |

My answer to my question

I was able to get the final answer using the PIVOT function

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') +  
   QUOTENAME(CompName) from B

 set @SQLQuery=N'select pvt.title as object, ' +   @PivotColumns + '
 FROM
 (select title, CountT,CompName
 from T 
  inner join A on T.IDA = A.ID
  inner join B  on   B.ID = T.IDA) AS src PIVOT
  (
 max(CountT)
 FOR CompName IN (' + @PivotColumns + ')
 ) AS pvt;'
 EXEC sp_executesql @SQLQuery

Upvotes: 0

Views: 53

Answers (1)

Horaciux
Horaciux

Reputation: 6477

select a.title as object, 
    sum(case when b.id=1 then T.countT else 0 end) as Comp1,
    sum(case when b.id=2 then T.countT else 0 end) as Comp2,
    sum(case when b.id=3 then T.countT else 0 end) as Comp3,
    sum(t.countt) as 'Sum',
    max(a.count)-sum(t.countt) as Remaining
 from TableT t
    inner join tableA A on a.id=t.IDA
    inner join TableB b on b.id=t.IDB    
    group by a.Title

http://rextester.com/l/sql_server_online_compiler

Upvotes: 2

Related Questions