SQL query to get horizontal table instead of vertical data?-CodePudding

I had three tables as below:

Table : tblAttribute

Id	attribute	dcControl
1	LowTv
2	fastNozzle
3	LowNOx	PAR_LOW_NOX_ENG_ENABLE
4	TCCutOff
5	WHR
6	gtd311
7	none

Table : tblTuning

id	eng_id	legislation	tuningMode
1	1	T2	Delta
2	1	T1	Delta
3	1	T1	LLT
4	1	T2	LLT
5	1	T2	Std
6	1	T1	Std
7	2	T2	Delta
8	2	T1	Delta
9	2	T1	LLT
10	2	T2	LLT
11	2	T1	Std
12	2	T2	Std

Table : tblTuningMatrix

id	itemID	tuningattr_id	tuningattr_value
1	1	1	1
2	2	1	0
3	3	1	1
4	4	1	0
5	5	1	0

Now my query it to get the data from these three table is as below:

select tuning.id, tuning.eng_id,
    tuningMatrix.tuningattr_value, tuning.tuningMode,
    tuning.legislation, tuningAttribute.attribute,
    tuningAttribute.dcControl
from tuningMatrix
inner join tuning on tuning.itemId=tuningMatrix.itemID
inner join tuningAttribute on tuningAttribute.id=tuningMatrix.tuningattr_id
WHERE tuning.deleted = 'false'

Using above query I am getting data in vertical table. I want it as horizontal. What can I do?

Expected output is :

id	eng_id	tuningMode	legislation	dcControl	LowTv	WHR	LowNox	TCCutOff	gtd311	fastNozzle
1	1	Delta	T2	null	0	0	0	1	1	0

Actual Output:

id	eng_id	tuningMode	legislation	dcControl	LowTv	WHR	LowNox	TCCutOff	gtd311	fastNozzle
1	1	Delta	T2		1	0	NULL	0	0	0
1	1	Delta	T2	PAR_LOW_NOX_ENG_ENABLE	NULL	NULL	0	NULL	NULL	NULL
2	1	Delta	T2		0	0	NULL	0	0	0
2	1	Delta	T2	PAR_LOW_NOX_ENG_ENABLE	NULL	NULL	0	NULL	NULL	NULL

CodePudding user response：

Here is a simple PIVOT, but a conditional aggregation would offer more flexibility

Example

Select * 
 From (
        select tuning.id
              ,tuning.eng_id
              ,tuning.tuningMode
              ,tuning.legislation
              ,tuningAttribute.dcControl
              ,value = convert(int,tuningMatrix.tuningattr_value)
              ,tuningAttribute.attribute
         From tuningMatrix
         inner join tuning on tuning.itemId=tuningMatrix.itemID
         inner join tuningAttribute on tuningAttribute.id=tuningMatrix.tuningattr_id
         WHERE tuning.deleted = 'false'
      ) src
 Pivot ( max( value ) for attribute in ( [LowTv],[WHR],[LowNox],[TCCutOff],[gtd311],[fastNozzle] ) ) pvt