By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

Need to eliminate nulls and group on row number

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.     RowNum,
  3.     chem_name,
  4.     amt,
  5.     measure
  6. FROM
  7.     (SELECT DISTINCT
  8.     ROW_INDEX as RowNum,
  9.         CASE WHEN COLUMN_NAME = 'Chemical Name' THEN ATTRIBUTE_VALUE END chem_name ,
  10.         CASE WHEN COLUMN_NAME = 'Amount' THEN ATTRIBUTE_VALUE END amt ,
  11.         CASE WHEN COLUMN_NAME = 'Measurement' THEN ATTRIBUTE_VALUE END measure
  12.     FROM BAppspectable_value batv
  13.  
  14.         INNER JOIN ACCOUNTING_AUDIT_TRAIL aat
  15.         ON aat.b1_per_id1 = batv.b1_per_id1
  16.         AND aat.b1_per_id2 = batv.b1_per_id2
  17.         AND aat.b1_per_id3 = batv.b1_per_id3
  18.  
  19.     WHERE batv.COLUMN_NAME IN ('Chemical Name','Amount', 'Measurement')
  20.  
  21.     AND aat.B1_ALT_ID = 'FP-15414') AS HAZMAT_LIST
  22.  
  23. RowNum    chem_name    amt    measure
  24. 0    NULL        NULL    Cuft
  25. 0    NULL        132    NULL
  26. 0    Acetylene    NULL    NULL
  27. 1    NULL        NULL    Cuft
  28. 1    NULL        200    NULL
  29. 1    Propane        NULL    NULL
  30. 2    NULL        NULL    Cuft
  31. 2    NULL        250    NULL
  32. 2    Oxygen        NULL    NULL
Jan 28 '15 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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/...=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.
Jan 28 '15 #2

P: 4
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.
Jan 28 '15 #3

Rabbit
Expert Mod 10K+
P: 12,430
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.
Jan 28 '15 #4

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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     [Chemical Name] AS chem_name,
  3.     [Amount] AS amt,
  4.     [Measurement] AS measure
  5. FROM 
  6. (SELECT
  7.     ROW_INDEX,
  8.     ATTRIBUTE_VALUE
  9.     FROM BAppspectable_value batv
  10.  
  11.         INNER JOIN ACCOUNTING_AUDIT_TRAIL aat
  12.         ON aat.b1_per_id1 = batv.b1_per_id1
  13.         AND aat.b1_per_id2 = batv.b1_per_id2
  14.         AND aat.b1_per_id3 = batv.b1_per_id3
  15.     WHERE aat.B1_ALT_ID = 'FP-15414') p
  16. PIVOT
  17. (
  18. MAX (ROW_INDEX)
  19. FOR ATTRIBUTE_VALUE
  20.     IN ([Chemical Name], [Amount], [Measurement])
  21. ) AS PivotTable
  22.  
  23.  
  24. What I am seeking is results like this.
  25. RowNum    chem_name    amt    measure
  26. 0    Acetylene    132    Cuft
  27. 1    Propane    200    Cuft
  28. 2    Oxygen     250    Cuft
Jan 28 '15 #5

Rabbit
Expert Mod 10K+
P: 12,430
Close, but you're missing a column and you put the wrong column in a few areas.

Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. ROW_INDEX,
  3. [Chemical Name] AS chem_name,
  4.  [Amount] AS amt,
  5.  [Measurement] AS measure
  6.  FROM 
  7.  
  8. (SELECT
  9.  ROW_INDEX,
  10.  COLUMN_NAME,
  11.  ATTRIBUTE_VALUE
  12.  FROM BAppspectable_value batv
  13.  
  14. INNER JOIN ACCOUNTING_AUDIT_TRAIL aat
  15.  ON aat.b1_per_id1 = batv.b1_per_id1
  16.  AND aat.b1_per_id2 = batv.b1_per_id2
  17.  AND aat.b1_per_id3 = batv.b1_per_id3
  18.  WHERE aat.B1_ALT_ID = 'FP-15414') p
  19.  PIVOT
  20.  (
  21.  MAX (ATTRIBUTE_VALUE)
  22.  FOR COLUMN_NAME
  23.  IN ([Chemical Name], [Amount], [Measurement])
  24.  ) AS PivotTable 
Jan 28 '15 #6

P: 4
Wow! Amazing thanks so much for your help, patience and for teaching me.
Jan 28 '15 #7

Rabbit
Expert Mod 10K+
P: 12,430
No problem! Good luck with the rest of your project
Jan 28 '15 #8

Post your reply

Sign in to post your reply or Sign up for a free account.