MS query to obtain a single row of data with multile rows

Kinderly Wade
Kinderly Wade used Ask the Experts™
on
Dear experts,

I would like to find out how can I modify my query to change the way my query result displays.

Here is my query:

SELECT product_id, warehouse_loc
FROM inventory
ORDER BY product_id

My display result will look like this:

product_id              warehouse_loc
NX-1233-01             B1-2
NX-1223-01             B2-2
Nx_1223-01             B3-1

Is there a way that I can modify my result to look like this:

product_id              warehouse_loc
NX-1233-01             B1-2
                                B2-2            
                                B3-1

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Not without a fair amount of pain.  It is the opinion of most developers that cosmetics like this are better served in whatever reporting application you are using as opposed to in T-SQL, as doing it that way in a set would hose up grouping/sorting.

For example, in SQL Server Reporting Services (SSRS) in the textbox for the product_id column the HideDuplicates property can be set to True, which would blank out all 'duplicates' beyond the first row.
Most Valuable Expert 2011
Top Expert 2012
Commented:
SELECT CASE WHEN rn = 1 THEN product_id END product_id, warehouse_loc
    FROM (SELECT product_id,
                 warehouse_loc,
                 ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY warehouse_loc) rn
            FROM inventory)
ORDER BY product_id, warehouse_loc;


This simply numbers each row 1,2,3,... for each product id,
 if row number = 1 (i.e. the first row for that product)  then display the id, otherwise return NULL (blank) for the product id
Do you want 3 rows with blank in 2 and 3rd row for first column?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<For example, in SSIS in the textbox for the product_id column the HideDuplicates property can be set to True, which would blank out all 'duplicates' beyond the first row.>>

 Same is true in the Access report writer, but not in a query.

Jim.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
typo correction, in my original comment I had SSIS instead of SQL Server Reporting Services (SSRS).   Corrected.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial