Different field contents to different columns...

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

General SQL Server Forums

New to SQL Server Programming

Different field contents to different columns...

Author

Topic

The Sweg
Starting Member

29 Posts

Posted - 2010-04-19 : 12:51:09

I'm very new to SQL and appreciate any help.

My table contents:
Record_____Widget#_____Ship_Status_____Action_Date
__1_________W1__________LOADED__________4/19/2010
__2_________W1__________SHIPPED_________4/20/2010
__3_________W2__________LOADED__________4/19/2010
__4_________W2__________SHIPPED_________4/20/2010
__5_________W3__________LOADED__________4/19/2010
__6_________W4__________SHIPPED_________4/21/2010

My desired output:
Widget#_____Ship Status1___Action Date1___Ship Status2___Action Date2
__W1_________LOADED_________4/19/2010______SHIPPED________4/20/2010
__W2_________LOADED_________4/19/2010______SHIPPED________4/20/2010
__W3_________LOADED_________4/19/2010______SHIPPED________4/21/2010

In reality, there will be about 6 different shipping statuses (unloaded, staged, etc...) But if I can get this to work, I think I can apply it to my real situation. The main problem is that I cannot have any duplicate records in my query result, so each different record for a particular widget needs to be in the same row, only in it's own column with a column that shows the date of that action.

I am using SQL Server 2000.

Again, thanks for any help!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-19 : 13:31:44

[CODE]
create table #tab1 (record int, widget char(2), ship_status char(10), action_date date)

insert into #tab1
select 1, 'W1','LOADED','4/19/2010'
UNION ALL
SELECT 2, 'W1','SHIPPED','4/20/2010'
UNION ALL
SELECT 3, 'W2','LOADED','4/19/2010'
UNION ALL
SELECT 4, 'W2','SHIPPED','4/20/2010'
UNION ALL
SELECT 5, 'W3','LOADED','4/19/2010'
UNION ALL
SELECT 6, 'W3','SHIPPED','4/20/2010'

SELECT WIDGET, S1, ACTDATE1, S2, ACTDATE2
FROM (
SELECT A.RECORD, A.WIDGET, A.SHIP_STATUS AS S1, A.ACTION_DATE AS ACTDATE1, B.SHIP_STATUS AS S2, B.ACTION_DATE AS ACTDATE2
, (SELECT TOP 1 RECORD FROM #TAB1 WHERE WIDGET = A.WIDGET) AS RNK
FROM #TAB1 A JOIN #TAB1 B
ON A.WIDGET = B.WIDGET AND A.RECORD <> B.RECORD
)T
WHERE T.RNK = T.RECORD
[/CODE]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-19 : 13:35:25

Try this:

Try this:

Create table Data
(
Record int identity,
Widget Varchar(10),
Ship_status Varchar(10),
Action_Date datetime
)

Insert into Data
Select 'W1','LOADED','2010-04-19' union
Select 'W1','SHIPPED','2010-04-20' union
Select 'W2','LOADED','2010-04-19' union
Select 'W2','SHIPPED','2010-04-20' union
Select 'W3','LOADED','2010-04-19' union
Select 'W3','SHIPPED','2010-04-21'

Select l.Widget, l.Ship_status as Ship_Status1, l.Action_Date as Action_Date1
, S.Ship_status as Ship_Status2, s.Action_Date as Action_Date2
From Data l left outer join Data s on l.widget = s.widget and
s.Ship_status='SHIPPED'
where l.Ship_status ='LOADED'

Regards,
Bohra

The Sweg
Starting Member

29 Posts

Posted - 2010-04-19 : 13:59:52

Thank you for your responses. I will give this a try and let you know how it goes!

The Sweg
Starting Member

29 Posts

Posted - 2010-04-21 : 11:14:17

pk bohra,

OK, so I copied and pasted everything in the Query analyzer and it worked just fine and gave the output I expected, but I don't really know what I'm looking at or why it worked. I understand the create table and insert into statements, but the select statement has me confused. Why is there a 'l.' or 's.' in front of the column names? Is this critical to how the statement works? Also, how would I apply this to my table with 6 different choices for the ship status (ie. unloaded, loaded, to painter, staged, QC, shipped)?

thanks again for your help and patience!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 12:06:01

quote:
Originally posted by The Sweg

OK, so I copied and pasted everything in the Query analyzer and it worked just fine and gave the output I expected, but I don't really know what I'm looking at or why it worked. I understand the create table and insert into statements, but the select statement has me confused. Why is there a 'l.' or 's.' in front of the column names? Is this critical to how the statement works? Also, how would I apply this to my table with 6 different choices for the ship status (ie. unloaded, loaded, to painter, staged, QC, shipped)?

l and s are the alias for the Data table. Since both the table are alias for single table (Tablename: Data), if the table alias is not specified before the column you will get an error because SQL server will not know the column needs to be picked from first table or second table.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 12:06:37

Try this for 6 different status:

Assumption of order of activity.

Loaded -> Shipped -> QC-> Staged -> Painter ->unloaded

Create table Data
(
Record int identity,
Widget Varchar(10),
Ship_status Varchar(10),
Action_Date datetime
)

Insert into Data
Select 'W1','LOADED','2010-04-19' union
Select 'W1','SHIPPED','2010-04-20' union
Select 'W1','QC','2010-04-21' union
Select 'W1','staged','2010-04-22' union
Select 'W1','painter','2010-04-23' union
Select 'W1','unloaded','2010-04-25' union
Select 'W2','LOADED','2010-04-19' union
Select 'W2','SHIPPED','2010-04-20' union
Select 'W2','QC','2010-04-22' union
Select 'W2','staged','2010-04-25' union
Select 'W2','painter','2010-04-26' union
Select 'W2','unloaded','2010-04-28' union
Select 'W3','LOADED','2010-04-19' union
Select 'W3','SHIPPED','2010-04-21' union
Select 'W3','QC','2010-04-22' union
Select 'W3','staged','2010-04-23' union
Select 'W3','painter','2010-04-25' union
Select 'W3','unloaded','2010-04-27'

Select l.Widget, l.Ship_status as Ship_Status1,
l.Action_Date as Action_Date1
, S.Ship_status as Ship_Status2, s.Action_Date as Action_Date2,
Q.Ship_status as Ship_Status3, Q.Action_Date as Action_Date3,
SG.Ship_status as Ship_Status4, SG.Action_Date as Action_Date4,
P.Ship_status as Ship_Status5, P.Action_Date as Action_Date5,
U.Ship_status as Ship_Status6, U.Action_Date as Action_Date6

From Data l left outer join Data s on l.widget = s.widget and
s.Ship_status='SHIPPED' left outer join Data u on l.widget = u.widget
and u.Ship_status = 'unloaded'
left outer join Data q on l.widget = q.widget
and q.Ship_status = 'QC'
left outer join Data p on l.widget = p.widget
and p.Ship_status = 'painter'
left outer join Data sg on l.widget = sg.widget
and sg.Ship_status = 'staged'
where l.Ship_status ='LOADED'

Regards,
Bohra

The Sweg
Starting Member

29 Posts

Posted - 2010-04-22 : 08:23:23

Bohra,

Thanks for the help. I think I am starting to understand what is going on in the SQL select statement. I appears that you are using the first letter of what the value of the field might be to separate it into the different categories. Could I use any unique letter or letter combination to differentiate the new columns?

Thanks again,

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-22 : 09:52:54

quote:
Originally posted by The Sweg

Bohra,

Thanks for the help. I think I am starting to understand what is going on in the SQL select statement. I appears that you are using the first letter of what the value of the field might be to separate it into the different categories. Could I use any unique letter or letter combination to differentiate the new columns?

Thanks again,

Yes you can change the alias name to any unique character or combination of character...
Try playing with the select statement. By playing around the select statement you will learn more and will learn yourself.
If any problem comes, there are many volunteers here to help you out..

Enjoy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources