SQL - Selecting different (4) values from one column and displaying into four separate columns

Question

I've been given a database of geoscience data, with the task of querying out sample location, as well as analytical results for several elements, gold arsenic, lead, and copper. In previous databases I've worked with the analytical results have been listed in a table, where each header is an element and the primary key is the sample ID.

This gov't database has listed the elements in a column

(dbo.SurfaceSampleAttr.AttributeColumn), and their results in a second column (dbo.SurfaceSampleAttr.AttributeValue). I've managed to be able to query out one element with the below code.

SELECT
    dbo.SurfaceSample.SampleId, 
    dbo.SurfaceSample.CompanySampleId,
    dbo.SurfaceSampleCoordinate.Easting,
    dbo.SurfaceSampleCoordinate.Northing,
    dbo.SurfaceSampleCoordinate.Datum,
    dbo.SurfaceSampleCoordinate.Projection,
    dbo.SurfaceSampleCoordinate.Zone,
    dbo.SurfaceSample.CompanyId,
    dbo.SurfaceSample.SurfaceSampleType,
    dbo.MRTHeader.HeaderValue AS 'Tenement Holder',
    dbo.SurfaceSampleAttr.AttributeValue
FROM
    ((dbo.SurfaceSample
INNER JOIN 
    dbo.SurfaceSampleCoordinate ON dbo.SurfaceSample.Id = dbo.SurfaceSampleCoordinate.SurfaceSampleId)
LEFT JOIN 
    dbo.SurfaceSampleAttr ON dbo.SurfaceSampleCoordinate.SurfaceSampleId = dbo.SurfaceSampleAttr.SurfaceSampleId
                          AND ((dbo.SurfaceSampleAttr.AttributeColumn) = 'Au_ppb'))
INNER JOIN 
    dbo.MRTHeader ON dbo.SurfaceSample.CompanyId = dbo.MRTHeader.MRTFileId
WHERE
    (((dbo.SurfaceSampleCoordinate.Zone) = '50')
    AND ((dbo.MRTHeader.HeaderNumber) = 'H0101'));

My ideal end-state would be to have all of the sample location metadata, such as Sample ID, location, company etc listed, and then LEFT JOIN the analysis onto the right hand side with alias'; not all samples will have all (or any) analysis results so I don't want to filter those out.

I've tried adding additional bracketed SELECT criteria in the overall FROM, as well as trying to add additional JOINs in my above code, but I can't figure out how to give the same column a different alias with different criteria.

Edit:

I'd like something that functioned like this assuming the syntax was correct:

SELECT
    dbo.SurfaceSample.SampleId,
    dbo.SurfaceSample.CompanySampleId,
    dbo.SurfaceSampleCoordinate.Easting,
    dbo.SurfaceSampleCoordinate.Northing,
    dbo.SurfaceSampleCoordinate.Datum,
    dbo.SurfaceSampleCoordinate.Projection,
    dbo.SurfaceSampleCoordinate.Zone,
    dbo.SurfaceSample.Anumber,
    dbo.SurfaceSample.CompanyId,
    dbo.SurfaceSample.SurfaceSampleType,
    dbo.MRTHeader.HeaderValue AS 'Tenement Holder',
FROM
    (dbo.SurfaceSample
LEFT JOIN 
    (SELECT
         dbo.SurfaceSampleAttr.AttributeValue AS 'Au_ppb'
     FROM
         dbo.SurfaceSampleAttr
     WHERE
         (dbo.SurfaceSampleAttr.AttributeColumn) = 'Au_ppb') ON dbo.SurfaceSampleCoordinate.SurfaceSampleId = dbo.SurfaceSampleAttr.SurfaceSampleId
LEFT JOIN 
    (SELECT
         dbo.SurfaceSampleAttr.AttributeValue AS 'Cu_ppm'
     FROM
         dbo.SurfaceSampleAttr
     WHERE
         (dbo.SurfaceSampleAttr.AttributeColumn) = 'Cu_ppm') ON dbo.SurfaceSampleCoordinate.SurfaceSampleId = dbo.SurfaceSampleAttr.SurfaceSampleId
LEFT JOIN 
    (SELECT
         dbo.SurfaceSampleAttr.AttributeValue AS 'Ars_ppm'
     FROM
         dbo.SurfaceSampleAttr
     WHERE
         (dbo.SurfaceSampleAttr.AttributeColumn) = 'Ars_ppm') ON dbo.SurfaceSampleCoordinate.SurfaceSampleId = dbo.SurfaceSampleAttr.SurfaceSampleId
LEFT JOIN 
    (SELECT
         dbo.SurfaceSampleAttr.AttributeValue AS 'Pb_ppm'
     FROM
         dbo.SurfaceSampleAttr
     WHERE
         (dbo.SurfaceSampleAttr.AttributeColumn) = 'Pb_ppm') ON dbo.SurfaceSampleCoordinate.SurfaceSampleId = dbo.SurfaceSampleAttr.SurfaceSampleId)
WHERE
    (((dbo.SurfaceSampleCoordinate.Zone) = '50')
    AND ((dbo.MRTHeader.HeaderNumber) = 'H0101'));

The above screenshot is the dbo.SurfaceSampleAttr table, and the two columns I'm interested in are the .AttributeColumn and .AttributeValue. Instead of having the elements as headers they've listed them in a column.

Edit Tried the pivot approach. A step closer, but there are a lot of Null values

Hello Gene, can you document your question with sample data oriented and not only complex SQL ? You give give your table input and what you are expecting. — schlebe, Feb 28 '19 at 05:17
Edited to include a screenshot of the data I'm working with. It is just the table that I'm looking to query, the other tables are fairly straight forward. — Gene Parmesan, Feb 28 '19 at 05:27
Have you looked at pivoting, think that is what you need: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — peeyush singh, Feb 28 '19 at 05:32
The data is in normalized structure and you want to de-normalize for output presentation. Use a CROSSTAB query to pivot. Use the Access query designer. How many AttributeColumn elements are possible? A query can have up 255 fields. — June7, Feb 28 '19 at 05:51
Tried the pivot approach, added a screenshot of the result. The columns are arranged in the right way, but now there are multiple rows with the same ID, presumably all the different data that was originally in the pivoted column. — Gene Parmesan, Feb 28 '19 at 08:13

score 1 · Answer 1 · answered Feb 28 '19 at 08:28

Try pre-selecting columns before pivoting:

SELECT p.*
FROM (
    SELECT
        dbo.SurfaceSampleAttr.SampleId,
        dbo.SurfaceSampleAttr.AttributeColumn,
        dbo.SurfaceSampleAttr.AttributeValue
    FROM dbo.SurfaceSampleAttr
) t
PIVOT (
   Min(AttributeValue)
   FOR AttributeColumn IN ([Au_ppb], etc)
) p

Also, if you're using MS Access as a front-end, it offers a more flexible and powerful TRANSFORM ... PIVOT ... query that explicitly allows grouping by multiple columns when pivoting, and dynamically determines column names.

SQL - Selecting different (4) values from one column and displaying into four separate columns

1 Answers1