SQL query to get horizontal table instead of vertical data?

Question

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

From where you are referring the columns after legislation in the expected output — Roshan
– Roshan, Commented Oct 13, 2022 at 4:06

John Cappelletti · Accepted Answer · 2022-10-13 04:18:06Z

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

But I am not able to apply max() function on tuningattr_value because it's bit operand.
I had tried the same query and getting error like : Msg 8117, Level 16, State 1, Line 30 Operand data type bit is invalid for max operator.
@SrustiThakkar See update. I just convert tuningattr_value to an INT

Collectives™ on Stack Overflow

SQL query to get horizontal table instead of vertical data?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related