Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-08-29 : 02:50:27

Hi all,

I have a table that among other things has product code and bin label.

There is a row in the table for each bin label.

I want to do a query that returns me a single row per product, with each bin label in a seperate column.

I thought pivot might achieve this but I'm having trouble getting it to work.

Any ideas how I can get the result I want?

Thanks!
Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-29 : 03:17:47

Yes PIVOT will do the job.

Post your table DDL, sample data and result that you want here.

KH
[spoiler]Time is always against us[/spoiler]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-08-31 : 19:07:33

Hi,

I need the following fields from table binlocat.
Product
Binlabel

An example of the data is:
Product Binlabel
100100 A01C03
100100 A01C04

I want to return this data as something like this:
Product Binlabel1 Binlabel2
100100 A01C03 A01C04

Hope this makes sense.

Thanks,
Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-31 : 22:07:38

[code]DECLARE @sample TABLE
(
Product varchar(10),
Binlabel varchar(10)
)

INSERT INTO @sample
SELECT '100100', 'A01C03' UNION ALL
SELECT '100100', 'A01C04'

SELECT Product, [1] AS Binlable1, [2] AS Binlable2
FROM
(
SELECT Product,
Binlabel,
row_no = row_number() OVER (PARTITION BY Product ORDER BY Binlabel)
FROM @sample
) s
pivot
(
MAX(Binlabel)
FOR row_no IN ([1], [2])
) p[/code]

KH
[spoiler]Time is always against us[/spoiler]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-08-31 : 22:51:19

Thanks heaps for that, works a treat!

However, I've now realised there are 2 more columns I want from that table. They are min_pack and max_pack, and are relevant to each binlabel. How can I include these? I've had a play around and can't seem to get it right.

Cheers!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-31 : 22:52:55

the min_pack & max_pack is per product or binlabel ?
how do you want to show these 2 column ?

KH
[spoiler]Time is always against us[/spoiler]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-08-31 : 23:04:44

They are per binlabel.

We currently have the following columns:
Product, Binlabel1, Binlabel2

I would want something like:
Product, Binlabel1, Binlabel1Max, Binlabel1Max, Binlabel2, Binlabel2Min, Binlabel2Max

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-31 : 23:15:43

[code]DECLARE @sample TABLE
(
Product varchar(10),
Binlabel varchar(10),
BinlabelMin varchar(10),
BinlabelMax varchar(10)
)

INSERT INTO @sample
SELECT '100100', 'A01C03', 'A01C03-MIN', 'A01C03-MAX' UNION ALL
SELECT '100100', 'A01C04', 'A01C04-MIN', 'A01C04-MAX'

SELECT Product,
Binlabel1 = MAX(CASE WHEN row_no = 1 THEN Binlabel END),
Binlabel1Min = MAX(CASE WHEN row_no = 1 THEN BinlabelMin END),
Binlabel1Max = MAX(CASE WHEN row_no = 1 THEN BinlabelMax END),
Binlabel2 = MAX(CASE WHEN row_no = 2 THEN Binlabel END),
Binlabel2Min = MAX(CASE WHEN row_no = 2 THEN BinlabelMin END),
Binlabel2Max = MAX(CASE WHEN row_no = 2 THEN BinlabelMax END)
FROM
(
SELECT Product, Binlabel, BinlabelMin, BinlabelMax,
row_no = row_number() OVER (PARTITION BY Product ORDER BY Binlabel)
FROM @sample
) s
GROUP BY Product[/code]

KH
[spoiler]Time is always against us[/spoiler]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-09-01 : 18:42:47

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources