February 9, 2015 at 9:39 pm
[font="Courier New"]Hi,
There are 2 tables
1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID
Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458
Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB
Desired output is
TempTable
TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA
Can you please suggest something.
Thanks in advance[/font]
February 9, 2015 at 11:15 pm
Welcome to SSC.
Thanks for the sample data. In the future, if you could post it like this, you'll get a lot more responses, and more quickly because people don't have to recreate your database from scratch. Since you're new here, I'll do my best Sean Lange impersonation and show you how it should be done.
Here's a great article that explains how to use STUFF to do what you want:
-- create table scripts
CREATE TABLE trnNum (
trnID INT
CONSTRAINT pkTrn PRIMARY KEY (trnID));
GO
CREATE TABLE trnPrd (
trnID INT
,PrdID CHAR(2)
CONSTRAINT pkProd PRIMARY KEY (trnID, PrdID),
CONSTRAINT fkPrdID FOREIGN KEY (trnID) REFERENCES trnNum(trnID));
GO
-- insert statements to populate the tables (so now folks here can recreate the relevant part of your problem/database).
INSERT INTO trnNum(trnID) VALUES (1234),(2565),(3458);
INSERT INTO trnPrd(trnID, PrdID) VALUES
(1234,'AA'), (1234,'BB'),(1234,'CC'),
(2565,'CC'), (2565,'EE'),(2565,'FF'),(2565,'HH'),
(3458,'AA'),(3458,'BB');
-- answer to your question (use the STUFF function) :
SELECT N.trnID
, CommaList = STUFF((
SELECT ', ' + PrdID
FROM trnPrd P
WHERE P.trnID = N.trnID
ORDER BY N.trnID
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM trnNum N
ORDER BY N.trnID;
Hope this helps!
Pieter
February 10, 2015 at 5:41 pm
pietlinden (2/9/2015)
-- answer to your question (use the STUFF function) :
SELECT N.trnID, CommaList = STUFF((
SELECT ', ' + PrdID
FROM trnPrd P
WHERE P.trnID = N.trnID
ORDER BY N.trnID
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM trnNum N
ORDER BY N.trnID;
Hope this helps!
Pieter
That produces this result, which is not quite what the OP asked for.
trnID CommaList
1234 AA, BB, CC
2565 CC, EE, FF, HH
3458 AA, BB
I see a couple of issues here:
- How to identify the first row where "START" appears?
- How to order, the trnPrd records after finding the first?
We'll assume that your PrdID implies the ordering, but I'm guessing in real life that probably isn't the case. Do not assume that insert order establishes the ordering. In this case, it coincidentally retrieves in that order because of the PRIMARY KEY pietlinden provided in his wonderfully helpful sample test data.
So now let's look at an alternate solution that staggers the results.
SELECT trnID, PrdID
,s='START' + ISNULL(': ' + s2, '')
FROM trnPrd a
OUTER APPLY
(
SELECT STUFF((
SELECT ', ' + PrdID
FROM trnPrd b
WHERE a.trnID = b.trnID AND b.PrdID < a.PrdID
ORDER BY b.PrdID
FOR XML PATH(''), TYPE).value('.','varchar(max)'
), 1, 2, '')
) b (s2)
ORDER BY trnID, PrdID;
SQL 2012 would have handled this a little better using the LAG function.
Edit: Slightly adjusted the result assigned to s to correct placement of the colon.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply