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
7 1553
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.
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.
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.
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
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
Wow! Amazing thanks so much for your help, patience and for teaching me.
No problem! Good luck with the rest of your project
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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" & "-" &...
|
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...
|
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...
|
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
...
|
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...
|
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:
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
| |