Need to eliminate nulls and group on row number

[rswhitaker]

I am struggling with some results in which I had to take values from a single column and derive separate columns. Now I have three columns and three rows that I wish to combine into a single row where the column RowNum is share. Any tips? The query and results are below.

SELECT DISTINCT
	RowNum,
	chem_name,
	amt,
	measure
FROM
	(SELECT DISTINCT
	ROW_INDEX as RowNum,
		CASE WHEN COLUMN_NAME = 'Chemical Name' THEN ATTRIBUTE_VALUE END chem_name ,
		CASE WHEN COLUMN_NAME = 'Amount' THEN ATTRIBUTE_VALUE END amt ,
		CASE WHEN COLUMN_NAME = 'Measurement' THEN ATTRIBUTE_VALUE END measure
	FROM BAppspectable_value batv

		INNER JOIN ACCOUNTING_AUDIT_TRAIL aat
		ON aat.b1_per_id1 = batv.b1_per_id1
		AND aat.b1_per_id2 = batv.b1_per_id2
		AND aat.b1_per_id3 = batv.b1_per_id3

	WHERE batv.COLUMN_NAME IN ('Chemical Name','Amount', 'Measurement')

	AND aat.B1_ALT_ID = 'FP-15414') AS HAZMAT_LIST

RowNum	chem_name	amt	measure
0	NULL		NULL	Cuft
0	NULL		132	NULL
0	Acetylene	NULL	NULL
1	NULL		NULL	Cuft
1	NULL		200	NULL
1	Propane		NULL	NULL
2	NULL		NULL	Cuft
2	NULL		250	NULL
2	Oxygen		NULL	NULL

[Rabbit]

You can do it all in one go without the intermediate query by using the PIVOT functionality in SQL Server.

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Go ahead and read the article and give it a try. If you run into any problems. Post the query you tried and we can help you troubleshoot it.

[rswhitaker]

I don't understand what aggregate function is being required by the PIVOT syntax. I am not aggregating anything. Also while I can create the columns using PIVOT, each of those columns needs different criteria to populate it from the source query.
Forgive me... I'm just not getting it.

[Rabbit]

The aggregate can be anything in your case. I recommend MAX because you only want one of the values in there.

The PIVOT FOR ... IN ... defines the criteria values that populates the different columns from the source query.

[rswhitaker]

Okay, so this is what I have but it returns a single row with all null values.

SELECT 
	[Chemical Name] AS chem_name,
	[Amount] AS amt,
	[Measurement] AS measure
FROM 
(SELECT
	ROW_INDEX,
	ATTRIBUTE_VALUE
	FROM BAppspectable_value batv

		INNER JOIN ACCOUNTING_AUDIT_TRAIL aat
		ON aat.b1_per_id1 = batv.b1_per_id1
		AND aat.b1_per_id2 = batv.b1_per_id2
		AND aat.b1_per_id3 = batv.b1_per_id3
	WHERE aat.B1_ALT_ID = 'FP-15414') p
PIVOT
(
MAX (ROW_INDEX)
FOR ATTRIBUTE_VALUE
	IN ([Chemical Name], [Amount], [Measurement])
) AS PivotTable


What I am seeking is results like this.
RowNum	chem_name	amt	measure
0	Acetylene	132	Cuft
1	Propane	200	Cuft
2	Oxygen 	250	Cuft

[Rabbit]

Close, but you're missing a column and you put the wrong column in a few areas.

Try this:

SELECT 
ROW_INDEX,
[Chemical Name] AS chem_name,
 [Amount] AS amt,
 [Measurement] AS measure
 FROM 
 
(SELECT
 ROW_INDEX,
 COLUMN_NAME,
 ATTRIBUTE_VALUE
 FROM BAppspectable_value batv
 
INNER JOIN ACCOUNTING_AUDIT_TRAIL aat
 ON aat.b1_per_id1 = batv.b1_per_id1
 AND aat.b1_per_id2 = batv.b1_per_id2
 AND aat.b1_per_id3 = batv.b1_per_id3
 WHERE aat.B1_ALT_ID = 'FP-15414') p
 PIVOT
 (
 MAX (ATTRIBUTE_VALUE)
 FOR COLUMN_NAME
 IN ([Chemical Name], [Amount], [Measurement])
 ) AS PivotTable

[rswhitaker]

Wow! Amazing thanks so much for your help, patience and for teaching me.

[Rabbit]

No problem! Good luck with the rest of your project