473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need to eliminate nulls and group on row number

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
7 1553
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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
Wow! Amazing thanks so much for your help, patience and for teaching me.
Jan 28 '15 #7
Rabbit
12,516 Expert Mod 8TB
No problem! Good luck with the rest of your project
Jan 28 '15 #8

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

Similar topics

1
by: tHeRoBeRtMiTcHeLL | last post by:
Below is an earlier post to an Excel Group.. ....but I thought that there might be a way to do this in Access by importing data and then creating append and/or update query. I would most certainly...
0
by: Eric | last post by:
Visual C++ 2005 Express MVP's and experience programmer's only please!... I need to get the number of lines in a textbox so I can insert them into a listview. The text comes from my database...
1
by: videostar | last post by:
Greetings, first post, but have read many times for various CF questions. My goal is, using CF and MS-SQL 2k, the following: and - I need to have either CF or MS-SQL upon a record...
4
by: rdemyan via AccessMonster.com | last post by:
I have the following SQL statement in code that is set to the RowSource of a combobox. The combobox has two columns. SELECT '(ALL)' As Site, '' As , 0 As SortFirst FROM GROUP BY SITE_COMPLEX...
5
by: jdvon | last post by:
So I have downloaded this neat expression for a textbox control in a form that generates automatic sequence numbers with the 2-digit year in front of it: =Format(Now(),"yy") & "CAR" & "-" &...
10
by: strife | last post by:
Hi, This is a homework question. I will try to keep it minimal so not to have anyone do it for me. I am really just stuck on one small spot. I have to figure out the highest number from a users...
11
by: browndudley | last post by:
I searched the forum and found something very close to helping me with what I'm trying to do but not exactly. I need to sequentially group number the results in an access query as if by grouping...
2
by: siva125 | last post by:
i use row_number() in my sql query to generate number from 1...,but i need to start with row number from 100,101 like wise i using sql4.2 navigator ,i use auto increment it show error what to do ...
0
by: Vikki McCormick | last post by:
Hi, The infrastructure team is making a lot of physical changes to the servers. We are moving out of one place to another. Developers used to be able to just connect to a SQL instance no problem. I...
3
by: Gulo | last post by:
Hello, I need to extract the number of google plus likes of some pages, for example bytes.com the number is here: ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.