July 21, 2015 at 8:41 am
Good Morning All:
Can someone helpme with this query?
I need a query that when i provide a data in this case '313126459' it check if exixt in BC2, then if this exist take the number in BC1 and check again in BC2, if exist checkagain in
BC2...
IDBC1BC2Date_TimeNodeType
5971004938045GA5273131264592015-07-21 08:21:48.973K40-C2-ROUTER-15
59714582CCQFN45E10 12201338045GA5272015-07-21 08:51:30.630K40-L2-LINK-37
59712871200AZ5415W2116CCQFN45E10 1220132015-07-21 08:40:54.880K36-NISSAN-MAINSEAL127
At the end I need a result like this
SerialLink1Link2Link3
31312645938045GA527CCQFN45E10 122013200AZ5415W2116
thanks in advance
July 21, 2015 at 9:25 am
laorozco1 (7/21/2015)
Good Morning All:Can someone helpme with this query?
I need a query that when i provide a data in this case '313126459' it check if exixt in BC2, then if this exist take the number in BC1 and check again in BC2, if exist checkagain in
BC2...
IDBC1BC2Date_TimeNodeType
5971004938045GA5273131264592015-07-21 08:21:48.973K40-C2-ROUTER-15
59714582CCQFN45E10 12201338045GA5272015-07-21 08:51:30.630K40-L2-LINK-37
59712871200AZ5415W2116CCQFN45E10 1220132015-07-21 08:40:54.880K36-NISSAN-MAINSEAL127
At the end I need a result like this
SerialLink1Link2Link3
31312645938045GA527CCQFN45E10 122013200AZ5415W2116
thanks in advance
Hi and welcome to the forums. From what you posted it is quite difficult to figure out what you are trying to do. There seems to be a couple things you need. First is a query to return the rows, that should be pretty simple. The second is converting those rows into columns. For this a crosstab is an excellent method. Take a look at the links in my signature about crosstabs. If you still need help then please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 21, 2015 at 9:33 am
Hi,
As mentioned by Sean, it's better if you provide sample data in a consumable format. This time I did it because I have free time.
The logic wasn't very clear, but I believe that I got what you wanted. One option is using an rCTE and the other is using several joins.
CREATE TABLE SampleData(
ID int,
BC1 varchar(100),
BC2 varchar(100),
Date_Time datetime,
Node varchar(100),
Type int
)
INSERT INTO SampleData VALUES
(59710049,'38045GA527','313126459', '2015-07-21 08:21:48.973', 'K40-C2-ROUTER-1', 5),
(59714582,'CCQFN45E10 122013','38045GA527', '2015-07-21 08:51:30.630', 'K40-L2-LINK-3', 7),
(59712871,'200AZ5415W2116','CCQFN45E10 122013', '2015-07-21 08:40:54.880', 'K36-NISSAN-MAINSEAL1', 27);
WITH rCTE AS(
SELECT *, 1 AS n
FROM SampleData
WHERE BC2 = '313126459'
UNION ALL
SELECT s.*, r.n + 1
FROM SampleData s
JOIN rCTE r ON s.BC2 = r.BC1
)
SELECT MAX(CASE WHEN n = 1 THEN BC2 END) Serial,
MAX(CASE WHEN n = 1 THEN BC1 END) Link1,
MAX(CASE WHEN n = 2 THEN BC1 END) Link2,
MAX(CASE WHEN n = 3 THEN BC1 END) Link3
FROM rCTE;
SELECT s1.BC2 Serial,
s1.BC1 Link1,
s2.BC1 Link2,
s3.BC1 Link3
FROM SampleData s1
JOIN SampleData s2 ON s1.BC1 = s2.BC2
JOIN SampleData s3 ON s2.BC1 = s3.BC2
WHERE s1.BC2 = '313126459';
Both solutions work different and might present different issues. Be sure to understand them and ask any questions that you have.
July 21, 2015 at 10:34 am
Thanks a lot for you help
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply