Comma Delimited Field Value - 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 2000 Forums

Transact-SQL (2000)

Comma Delimited Field Value

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-19 : 14:30:11

I sign for fame not for shame but all the same, I sign my name.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-04-19 : 14:43:16

Well

First off, the table design appears to be pretty "bad" for doing what you want.
but I am no sql guru.

I think creating a temp table of the stages is your best bet. then join back against the main table.

________________________________________________

Bier is a privelege of the working man (or woman).

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-19 : 15:00:23

Ok
Please, show me table structure both main and temp and how i will manipulate it.
Thanks

I sign for fame not for shame but all the same, I sign my name.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-19 : 15:04:18

Do not design your database to accept multiple values into 1 column.

see:

http://www.datamodel.org/NormalizationRules.html

The values should be broken out into a related table, 1 per column.

- Jeff

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-19 : 15:10:14

Yes, you are right, but i want a case where i will be able to store stages level along with the records and i will be able to sort like example i gave in the first post

stage 1
--------
record 1
record 3

stage 2
--------
record 1
record 3

stage 3
--------
record 1
record 2

stage 4
--------
record 2

Thanks,

I sign for fame not for shame but all the same, I sign my name.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-04-19 : 15:55:17

where is the problem, you don't know how to create a temp table?
or break out the data in your current model?

asking "do this all for me" probably won't get you too far.

________________________________________________

Bier is a privelege of the working man (or woman).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-19 : 16:22:23

I still don't understand what you are asking. But if you have a table of items:


create table Items (ItemID int primary key, ItemName varchar(100))

-- insert sample data:

insert into Items
select 1, 'Item 1' union
select 2, 'Item 2'

And you wish to indicate that the items must go through various Stages, you can have a table called ItemStages:


Create table ItemStages(ItemID int references Items (ItemID), Stage int, 
     constraint IS_PK primary key (ItemID,Stage))

-- sample data:
insert into ItemStages
select 1,1 union
select 1,3 union
select 1,5 union
select 2,3 union
select 2,4 union
select 2,5

-- (in the above, note that item 1 goes through stages 1,3,5 and Item 2 goes through 3,4,5)

And then to return the data you want it is a simple join:


select ItemStages.Stage, ItemStages.ItemID, Items.ItemName
from ItemStages 
inner join Items
on ItemStages.ItemID = Items.ItemID
order by Stage, ItemID

format those results as needed at your presentation layer, to add groups or indents or whatever you need. SQL's job is to return the data itself, not provide the results in a specific format.

do you see how I made it clear the tables and relations we are working with, along with providing sample data and cut and paste code? if you can do the same, we'll be able to help you much easier, don't you think?

- Jeff

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-20 : 07:02:47

Thanks all,
I now know i have the problem of presenting my question.

Sorry Vivaldi, you said "asking 'do this all for me' probably won't get you too far.", i have actually done some thing, and it worked, am only trying to streamline my results. I just visualised the model i gave in the post. Thanks for making to see it wrong.

This is the scenario. Thanks all.

CREATE PROCEDURE execption
AS
begin
select * into #temp
from
(
select fileid, 'Available' as Descriptions, 'fga' as BenchID, SetupDate as 'DDate', 0 as Metric, 'fga' as TriggeredBy, 0 as stageID, 'Available' as ExceptionHeader from MasterFile union

select fileid, 'FormMPrepared' as Descriptions, 'mpr' as BenchID, FormMPrepared as 'DDate', 0 as Metric, 'fga' as TriggeredBy, 1 as stageID , 'Form M not Ready' as ExceptionHeader from detest union

select fileid, 'FormMSentBank' as Descriptions, 'msb' as BenchID, FormMSentBank as 'DDate' , 0 as Metric, 'mpr' as TriggeredBy, 2 as stageID , 'Form M Not sent to Bank' as ExceptionHeader from detest union

select fileid, 'FormMApproved' as Descriptions, 'map' as BenchID, FormMApproved as 'DDate' , 0 as Metric, 'msb' as TriggeredBy, 3 as stageID , 'Form M Not Approved' as ExceptionHeader from detest union

select fileid, 'FormMSupplier' as Descriptions, 'ams' as BenchID, FormMSupplier as 'DDate' , 1 as Metric, 'map' as TriggeredBy, 4 as stageID , 'Form M not sent to supplier' as ExceptionHeader from detest union

select fileid, 'FormMInspection' as Descriptions, 'mia' as BenchID, FormMInspection as 'DDate' , 2 as Metric, 'map' as TriggeredBy, 5 as stageID , 'Form M Not sent to Inspection Agency' as ExceptionHeader from detest union

select fileid, 'FormMTransmitted' as Descriptions, 'mtr' as BenchID, FormMTransmitted as 'DDate' , 2 as Metric, 'mia' as TriggeredBy, 6 as stageID , 'Form M not transmitted' as ExceptionHeader from detest union

select fileid, 'LC Request' as Descriptions, 'cmr' as BenchID, LCCRequest as 'DDate' , 0 as Metric, 'mpr' as TriggeredBy, 7 as stageID , 'No Comfirmation Request' as ExceptionHeader from detest union

select fileid, 'LC Sent To Bank' as Descriptions, 'cfb' as BenchID, LCCBank as 'DDate' , 2 as Metric, 'cmr' as TriggeredBy, 8 as stageID , 'Comfirmation Letter Not Sent' as ExceptionHeader from detest union

select fileid, 'LC Bidding' as Descriptions, 'lcb' as BenchID, LCCBidding as 'DDate' , 2 as Metric, 'cmr' as TriggeredBy, 9 as stageID , 'Bid Not Done' as ExceptionHeader from detest union

select fileid, 'LC Comfirmed' as Descriptions, 'lcc' as BenchID, LCCComfired as 'DDate' , 2 as Metric, 'cfb' as TriggeredBy, 10 as stageID , 'LC Not Comfirmed' as ExceptionHeader from detest union

select fileid, 'LC Issued' as Descriptions, 'lci' as BenchID, LCCIssue as 'DDate' , 2 as Metric, 'msb' as TriggeredBy, 11 as stageID , 'LC Not Issued' as ExceptionHeader from detest union

select fileid, 'LC Telex Recieved' as Descriptions, 'lcr' as BenchID, LCCTelex as 'DDate' , 2 as Metric, 'lci' as TriggeredBy, 12 as stageID , 'LC Telex Not Recieved' as ExceptionHeader from detest union

select fileid, 'LC Comfirmed Telex Recieved' as Descriptions, 'cft' as BenchID, LCCTelexRecieved as 'DDate' , 2 as Metric, 'lcc' as TriggeredBy, 13 as stageID , 'Comfirmed, Telex Not Recieved' as ExceptionHeader from detest union

select fileid, 'Inspection Scheduled' as Descriptions, 'ins' as BenchID, PreSInpection as 'DDate' , 2 as Metric, 'fga' as TriggeredBy, 14 as stageID , 'Inspection Not Scheduled' as ExceptionHeader from detest union

select fileid, 'Goods Inspected' as Descriptions, 'gin' as BenchID, PreSInspected as 'DDate' , 2 as Metric, 'ins' as TriggeredBy, 15 as stageID , 'Goods Not Yet Inspected' as ExceptionHeader from detest union

select fileid, 'CRF Issued and Checked' as Descriptions, 'crf' as BenchID, PreSCRF as 'DDate' , 3 as Metric, 'gin' as TriggeredBy, 16 as stageID , 'No CRF' as ExceptionHeader from detest union

select fileid, 'Ship Booked' as Descriptions, 'sbk' as BenchID, PreSBooked as 'DDate' , 2 as Metric, 'gin' as TriggeredBy, 17 as stageID , 'Ship Not Booked' as ExceptionHeader from detest union

select fileid, 'ETD set' as Descriptions, 'etd' as BenchID, PresSETD as 'DDate' , 2 as Metric, 'sbk' as TriggeredBy, 18 as stageID , 'No ETD' as ExceptionHeader from detest union

select fileid, 'Goods Loaded' as Descriptions, 'gld' as BenchID, PreSLoaded as 'DDate' , 2 as Metric, 'etd' as TriggeredBy, 19 as stageID , 'Goods Not Loaded' as ExceptionHeader from detest union

select fileid, 'Shipping Dox Prepared' as Descriptions, 'sdx' as BenchID, PreSDox as 'DDate' , 2 as Metric, 'sbk' as TriggeredBy, 20 as stageID , 'Shipping Dox Not Prepared By Supplier' as ExceptionHeader from detest

)t order by fileid

select t1.fileid as 'fileID',
t1.Metric,
t1.ExceptionHeader,
t1.Descriptions,
t2.ddate as 'Start',
t1.DDate as 'Done',
datediff(dd,t2.ddate, t1.ddate) - datediff(ww,t2.ddate, t1.ddate) * 2 as 'Actual'

from #temp t1 join #temp t2 on (t1.fileid = t2.fileid)
where t2.Benchid = t1.triggeredby and t1.ddate is null
order by t1.Descriptions, t1.fileid, t1.stageID

If execute this query, it will return all.

The stageid is the stage file must pass through. The idea is all files must have pass through stage 0 to stage 20. But there are files that does not require some stages, even if they are entered into the database, i used the same table to store them all (detest). So I have 20 stages for a file, and some stages are optional.
So what I want is in my query, I want ONLY VALID record(s) in a particular stage as result (to show).

jsmith8858 says "format those results as needed at your presentation layer, to add groups or indents or whatever you need. SQL's job is to return the data itself, not provide the results in a specific format.", Yes, I have done the presentation right.
Thanks.
Worried Abacus :-/

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources