certain rows to column
-
Hi Using SQL Server 2008 R2. I have a stock table, with the following fields: warehouse, product, long_description, physical_qty. All products exist in warehouse '01'. Some of the products exist in warehouse '03' Faulty (or NG) goods get transferred to warehouse 03. I need a query to show distinct(product), long_description, physical_qty (for warehouse 01), physical_qty (for warehouse 03) Example data: warehouse product long_description physical_qty 01 00-00001 Item 1 100 03 00-00001 Item 1 5 01 00-00002 Item 2 200 Desired Result: Product Description Stock_Qty NG_Qty 00-00001 Item 1 100 5 00-00002 Item 2 200 I have tried this, but don't get distinct products (i.e. Item 1 appears in two rows)
Select distinct(product), long_description, case warehouse when '01' then physical_qty else 0 end as [Stock_Qty], case warehouse when '03' then physical_qty else 0 end as [NG_Qty] from vektron2.scheme.stockm group by product, warehouse, long_description, physical_qty order by product
-
Hi Using SQL Server 2008 R2. I have a stock table, with the following fields: warehouse, product, long_description, physical_qty. All products exist in warehouse '01'. Some of the products exist in warehouse '03' Faulty (or NG) goods get transferred to warehouse 03. I need a query to show distinct(product), long_description, physical_qty (for warehouse 01), physical_qty (for warehouse 03) Example data: warehouse product long_description physical_qty 01 00-00001 Item 1 100 03 00-00001 Item 1 5 01 00-00002 Item 2 200 Desired Result: Product Description Stock_Qty NG_Qty 00-00001 Item 1 100 5 00-00002 Item 2 200 I have tried this, but don't get distinct products (i.e. Item 1 appears in two rows)
Select distinct(product), long_description, case warehouse when '01' then physical_qty else 0 end as [Stock_Qty], case warehouse when '03' then physical_qty else 0 end as [NG_Qty] from vektron2.scheme.stockm group by product, warehouse, long_description, physical_qty order by product
You need to work through this Pivot two or more columns in SQL Server 2005[^]article blatant plug of own article
Never underestimate the power of human stupidity RAH
-
You need to work through this Pivot two or more columns in SQL Server 2005[^]article blatant plug of own article
Never underestimate the power of human stupidity RAH
Thanks Mycroft - will give it a try tonight