Putting unique results of SELECT rows in one row

Question

I have a query returning the results I have but I am not sure how to approach changing it to a convetion that my program uses to send data:

SELECT 
       [contract_member_brg_attr].attr_val AS 'field_properties',
       [contract_attr].attr_val            AS 'contract_number',
       [other_contract_attr].attr_val      AS 'supplier_number',
       [MFR].ITEM_NAME                     AS 'supplier_name'
FROM   [contract_member_brg_attr]
       INNER JOIN [contract_member_brg]
               ON [contract_member_brg_attr].item_id =
                  [contract_member_brg].item_id
       INNER JOIN [contract_attr]
               ON [contract_attr].item_id =
                  [contract_member_brg].[contract_item_id]
                  AND [contract_attr].field_id = 413
        INNER JOIN [contract_attr] AS [other_contract_attr]
               ON [other_contract_attr].item_id =
                  [contract_member_brg].[contract_item_id]
                  AND [other_contract_attr].field_id = 234
        INNER JOIN [MFR] as [MFR]
               ON [MFR].ITEM_PK =
                  [other_contract_attr].attr_val;

Results:

My issue is that I want all unique values from these results to be on 1 row. SO in this case, it would be all of the field_properties and one of each contract_number, supplier_number, supplier_name.

How would I do this what approaches are available?

EDIT: THis is how I would want it to look:

                    all on one row:
                    column1= 388
                    column2 = FEB 22 2017
                    column3 = FEB 22 2017
                    column4 = test 2
                    column5 = test 3
                    column6 = true
                    column7 = b5v5b5b5bb5
                    column8 = A180
                    column9 = ABBOTT NUTRITION

@GordonLinoff I have edited with the results I want. Thanks. — David Tunnell, Feb 23 '17 at 21:07
You are looking for a dynamic pivot. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — S3S, Feb 23 '17 at 21:15

score 0 · Answer 1 · edited May 23 '17 at 11:53

Please look to my question about pivot. It has few successfull answers: How to apply pivot to result of query

SELECT *
FROM (
    SELECT
           'id',
           'field_properties',
           'contract_number',
           'supplier_number',
           'supplier_name'
    FROM (
        SELECT
              row_number() over (ORDER BY [contract_member_brg_attr].id) AS 'id' \\should be some analog for your DB
              [contract_member_brg_attr].attr_val AS 'field_properties' ...
              \\your original query
    )
)
pivot (
    MIN('field_properties') \\any agregation function
    FOR ID  IN (1, 2, 3, 4, 5, 6)
) pvt

Putting unique results of SELECT rows in one row

1 Answers1