Difficult t-sql problem - 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)

Difficult t-sql problem

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-08 : 19:20:02

Hello,
Maybe someone could help me with the following:

I have 2 tables. Detail table is as follows:
Id int
wt decimal(7,3)

so one might have
ID WT
1 2.3
1 4.5
1 9.3
1 3.2
1 3.2
2 2.3
2 1.4
4 5.6
etc.

Table 2 is empty and is as follows

ID int
v1 decimal(7,3)
v2 decimal(7,3)
v3 decimal(7,3)
v4 decimal(7,3)

I need to write a query to produce in Table 2 the following:
full and partial filled fields in records depending on the number of weights for each ID in detail table.

so ID 1 would have 1 record in table 2 with all fields filled with weights and 1 record with just 1 weight in it because there are 5 records in detail table with ID = 1.

So when I am finished I would have this for table 2

ID v1 v2 v3 v4
1 2.3 4.5 9.3 3.2
1 3.2
2 2.3 1.4
4 5.6

etc.

I wonder what the best way to do this would be. I can see it using various loops, but I thought maybe using partioning on the ID field of details table in some way might make it less complicated but I am not that familiar with these new techniques. I considered pivot too but could not figure how to use it in this case.

Any ideas would be greatly appreciate.
Thanks
smh

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-08 : 19:24:15

Sounds like it would be far easier to employ the PIVOT operator to spin this into a new table.

The problem you have with other methods is the ID column is not unique..how many versions of each ID can there be? (that is, what is the max number of columns for the Table2?)

Poor planning on your part does not constitute an emergency on my part.

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-08 : 20:43:14

The Detail table is actually the child of an Order table. So the Id's are the foreign keys of the Detail Table. Each Order can have any number of weights associated with it up to 100 in this detail table. So there could be 100 of the same ID's in the detail table. Dividing that by 4 would mean there would be for ID = 1, 25 rows in TAble 2. Each row has the ID and 4 of the 100 weights each. If there were 99 wts, then the last row would have only v1, v2, and v3 fields filled with wt values.

Why do I need this? I need to list these weights on a report with 4 wts to a line. So I will have a report grouped by ID, like

ID 1
2.3 4.5 9.3 3.2
3.2

ID 2
2.3 1.4

ID 4
5.6

This is why I created the TAble 2. EAch row will have the ID and 4 of the weights of that ID and a partial row with the weights do not divide equally by 4. So if the Detail table has 5 rows with ID = 1 (namely 5 wts for that ID), There will be in the report 1 row of 4 weights and 1 row of 1 weight.

I figured it would be easier to fiddle with tables in sql to put the data into the form in which it will print out. In the report I will just use table 2 grouped by ID, and print out the wts in line by line.

Hope that helps clarify this.

I couldn't figure out how to use pivot because there may be more than one row for each ID. In the case of ID = 1, there would be 2 rows of id = 1, but there could be up to 25 rows if there are 100 weights in the details table. If there were 99 weights for ID = 1, the last row would only have 3 weights in it: the field v4 would be empty.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources