|
Hi all,
I have a sql 2000 table like this:
ID col1 col2
7777 itemx 12/02/07 00:00:10
7777 itemy 12/02/07 10:00:00
7777 itemz 12/02/07 12:10:60
8888 itemA 12/02/07 01:01:00
888 itemB 12/02/07 02:00:00
.......................................................
I like to combine all rows with the same ID together like the followings:
7777 itemx itemy itemz 12/02/07
888 itemA itemB.........................
The question has 3 parts:
1) what is the best way to do this? using trigger/stored procedure or just a vb sub with somesort of datarepeater, datalist controls
2) can you show me some examples of each way if any?
3) Can crystal report do something like this?
I am open to create another table or just plain writting them out on page.
Thanks
|
|
|
|
|
This is a technique to return items names a comma-separated values for each ID
create function GetItems(@id int)
returns varchar(1000)
As
Begin
declare @items varchar(1000)
Set @items=''
Select @items=@items+','+col1 from Items where ID=@ID
return @items
End
Go
this UDF returns a comma separated values for a given ID
then select the values with distinct IDs:
Select distinct ID,dbo.GetItems(ID) as items from items
results should look like:
777 ,itemx,itemy,itemz
888 ,itemx,itremy
you can display these results the way you want in your code.
|
|
|
|
|
Thanks. It works fine.
|
|
|
|
|
One more question> How do I incorporate that with datalist or datarepeater meaning how can make them as seperate columns so I can have appropriate headers for them. Thanks
|
|
|
|
|
I will be describing an electrical system. Option(1) is proposed by someone else, and I proposed option(2). I am using the term 'node' as a point on a electrical bus and 'devices' repersents switches or breakers...
Two option:
(1)(a)1:M<---(b)M:M--->(c)M:1
(2)(a)1:M<===(b)1:M<---(c)1:M
(1)
Table (a)&(c) have a many to many relationship. Table (b) is used to make this relationship.
This is to say that many nodes can connect to many devices. This does not show how different devices might relate to each other nor does it give connectivity. To set up a query to get the rating of a bus can be done with some work.
(2)
Table (a)&(b) have a double one to many and table, meaning the PK of (a) is a FK in (b) twice.
Table (b)&(c) have a one to many.
The (a)&(b) relationship creates a 'section'(Point A to Point B). Then from that a device in table (c) sits on a specific 'section' in which that 'section' is tied to only two nodes
(or records in table (a)). This does give connectivity to the devices and getting the rating of a bus would be much easier.
Option(2) is my opion if one of yall is able to understand what I am presenting. Can I get some feed back on both options. Don't worry I do not get offened by constructive replies. I see how option(1) works but I believe option(2) is more effectent.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
The second design will perform better and is easier to get a report with the relations you need.
But I think it's not very flexible, what if a device is connected to more than 2 nodes? Or is this an impossible case?
|
|
|
|
|
hspc wrote: what if a device is connected to more than 2 nodes
We have decided that a device will connect to one or two nodes but not more than two.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
jason_lakewhitney wrote: We have decided that a device will connect to one or two nodes but not more than two
Does that reflect reality?
I worked on a project a couple of years ago where there was an assumption that we only needed three connections between certain things. Then the client came along and asked 10 connections which broke our design and we (or rather I) spent 3 months re-writing the code due to a bad assumption being made.
|
|
|
|
|
Okay - I'm not familiar with nodes and devices in an electrical system. However option one looks like it describes a true many-to-many relationship. i.e. one node connects to many devices, one device connects to many nodes, therefore many devices connect to many nodes.
I just want to clarify what you are describing
(1) Node Table:
PK: NodeId
(3) Device Table:
PK: DeviceId
(2) Intermediate Table:
PK: NodeId, DeviceId
jason_lakewhitney wrote: This does not show how different devices might relate to each other nor does it give connectivity. To set up a query to get the rating of a bus can be done with some work.
How devices relate to each other is a different relationship. You can set up that relationship too.
If it is heirarchical then the Device table will contain a ParentDeviceId column as a foreign key that links to another row. If you only ever need to get the immediate parent or child this is quite efficient. If you need to get all children then you need to set up a separate table to denormalise the relationship. This adds duplicate information in a more efficient format for the queries you will run.
If Devices relate in a many-to-many way, then you can set up another intermediate table that links the two devices together.
Does this help?
|
|
|
|
|
Yes and no, it can be a little diffcult to explain. I work in the planning department along side the P.E.s they have describe the theoretical, physical and bussiness models of this system. This is the part way the helpdesk tech. is not getting it.
I should mention that a node is just a 'point in space' not physical but more of a location.
Theoretical Model - many devices are connected to many node.
Physical Model - many devices are connected to the physical bus where nodes are located.
Bussiness Model - many device are connected to one physical bus 'section' where nodes are the end points of the 'section'.
I have ranted many times about my situation. I, a developer, was hired to do a job but a helpdesk tech. has somehow worked his way to be in charge of the database design. His problem, I believe, is he is concetrated only on the theoretical model and he can't (or won't) look further into the data models.
Well, thanks for everyones input.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
I agree with Colin, a real world DB setup would be the Many to Many relationship with an Associative Table between the two. Even if you never plan on having to utilize the many connections to many nodes, at least it would be setup if anything ever arose like that.
Cleako
|
|
|
|
|
The query I'm working on a query that selects all introduced leads. I have, of course, managed this part without a problem but the query needs to select only those that were introduced within the past month.
I have found the GETDATE keyword but how can i use this to find introduced leads. The leads have a date time stamp when they were created.
?
|
|
|
|
|
Use also DATEADD to work out when a month ago was.
e.g.
SELECT * FROM IntroductionLeads
WHERE CreatedDate >= DATEADD(month, -1, GETDATE())
|
|
|
|
|
Ok thanks for that. Works great. I take it there is no DATESUBTRACT then?
|
|
|
|
|
Sam Heller wrote: I take it there is no DATESUBTRACT then?
No, just supply a negative number to DATEADD.
|
|
|
|
|
SET @Stringer = '<br />
SELECT [ProductID],<br />
(SELECT Tagline FROM Products WHERE ProductID = P.ProductID) As Tagline,<br />
(SELECT U.[CompanyName] FROM Products K, Users U WHERE K.ProductID = P.ProductID AND K.UserID = U.UserID) As [Company Name],<br />
(SELECT Logo FROM Products WHERE ProductID = P.ProductID) As Logo,<br />
[Actual Speed],<br />
[Actual APR],<br />
[Actual Broker Fee],<br />
[Completion Ratio],<br />
CONVERT(decimal(18, 1),(([Speed] + [APR] + [BrokerFee] + [Completion Ratio] + [Commission] + [List Bid]) / 6)) AS HSScore <br />
FROM #temp P'
The following code does not seem to work. The error is produced from the line
(SELECT U.[CompanyName] FROM Products K, Users U WHERE K.ProductID = P.ProductID AND K.UserID = U.UserID) As [Company Name]
Is it not possible to put table aliases inside a string and then execute it?
|
|
|
|
|
I would have done them as joins rather than inner queries.
Sam Heller wrote: Is it not possible to put table aliases inside a string and then execute it?
Yes, you can use table aliases inside dynamic SQL.
|
|
|
|
|
Table aliases are fine within dynamic SQL.
What is the error message? Try adding PRINT @Stringer after this code and before the EXEC or sp_executesql that you are using to run it.
|
|
|
|
|
Stupid Stupid Stupid. Turns out I had set the varchar for the string to 500 and it query had just about reached that so the string was missing the FROM section of the query.
Thanks anyway though
|
|
|
|
|
You're not alone 
|
|
|
|
|
Hi ,
Please someone show me a code that updates records of a table one by one using a cursor . I counld'nt find one !.
|
|
|
|
|
|
Is there anybody who has a merge module or any other way of deploying SQL Server 2005? The InstallShield site says they still don't have it, and that the users should make their own custom setup procedures, and Microsoft still does not show the way of easily deploy SQL server 20005 Express with any installation. But they did ban the MSDE from Vista, so any installation that includes MSDE and is targeting Vista needs to be changed.
Thanks.
Sarajevo, Bosnia
|
|
|
|
|
Hello
I have an application writing Japanese characters to the database. When I use MS SQL 2000, it works fine - the complete string is stored, but when I use MSDE, the strings seems to be truncated 
Any ideas? Is there any setting in MSDE that I'm overlooking?
Thanks for any help
Chandra
|
|
|
|
|
Hi Guru's,
What's the trim command on SQL?
Thanks
Dom;)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
