Is there a way to write this in SQL
-
If my database table a is for example:
ColA ColB ColC
1 A NULL
2 B NULL
3 C 7
4 D NULL
5 E 6
6 F NULL
7 G 9
8 H NULL
9 J NULL
and my basic SQL is:
SELECT ColA, ColB
FROM dbo.a
WHERE ColA = 3;
can this be altered so that I get the output:
ColA ColB
3 C
3 G
3 J
i.e. for as long as ColC is not null it gets the record where the first found ColC is equal to ColA, but maintains the first found ColA value in the output.
Doug
-
so this is a hiarchy.
With SQL2005 you can use a CTE with a recursive part
With SQL2000 or SQL7 you'll have to build "recursivity" yourself.
e.g.
set nocount on
declare @Rows int,
@LevelNr int,
@TsRefresh datetime
--declare @Tables TABLE (
--TableName varchar(200),
--LevelID int)
set @TsRefresh = getdate()
--Primaire applicaties detecteren (Adam)
-- TRUNCATE TABLE --
DELETE FROM dbo.T_MenuItems_Explosion_WRK
set @LevelNr = 1
INSERT INTO dbo.T_MenuItems_Explosion_WRK (Main_Application_Id, Level_Nr, Menu_ID, Description_short, Description_Long, Rank, Menu_Action, Target, Parent_Menu_ID, Ts_Krea, User_Krea, Ts_Wijzig, User_Wijzig , Ts_Explosion ) --, User_Explosion)
SELECT p.Menu_ID, @LevelNr, p.Menu_ID, p.Description_short, p.Description_Long, p.Rank, p.Menu_Action, p.Target, p.Parent_Menu_ID, p.Ts_Krea, p.User_Krea, p.Ts_Wijzig, p.User_Wijzig , @TsRefresh
from T_MenuItems p
inner join
T_MenuItems A
on p.Parent_Menu_Id = A.Menu_Id
and a.Description_Short = 'Adam'
select @Rows = @@RowCount, @LevelNr = @LevelNr + 1
while @Rows > 1
begin
INSERT INTO dbo.T_MenuItems_Explosion_WRK (Main_Application_Id,Level_Nr, Menu_ID, Description_short, Description_Long, Rank, Menu_Action, Target, Parent_Menu_ID, Ts_Krea, User_Krea, Ts_Wijzig, User_Wijzig , Ts_Explosion ) --, User_Explosion)
SELECT e.Main_Application_Id, @LevelNr, I.Menu_ID, I.Description_short, I.Description_Long, I.Rank, I.Menu_Action, I.Target, I.Parent_Menu_ID, I.Ts_Krea, I.User_Krea, I.Ts_Wijzig, I.User_Wijzig , @TsRefresh
from T_MenuItems I
inner join T_MenuItems_Explosion_WRK E
on e.Level_Nr = @LevelNr - 1
and i.Parent_Menu_ID = E.Menu_Id
select @Rows = @@RowCount, @LevelNr = @LevelNr + 1
Print '-- level ' + str(@LevelNr - 1 ) + ' / ' + str(@Rows)
end
delete from T_MenuItems_Explosion
set nocount off
Insert into T_MenuItems_Explosion
select *
from T_MenuItems_Explosion_WRK
order by Main_Application_Id, parent_menu_id, Level_Nr, rank
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi all,
Just to appy alzdba's example to your situation, Doug...
--This SQL script is safe to run
declare @t table (ColA int, ColB varchar(1), ColC int)
insert @t
select 1, 'A', NULL
union all select 2, 'B', NULL
union all select 3, 'C', 7
union all select 4, 'D', NULL
union all select 5, 'E', 6
union all select 6, 'F', NULL
union all select 7, 'G', 9
union all select 8, 'H', NULL
union all select 9, 'J', NULL
declare @Root int
set @Root = 3
declare @U table (level int, ColA int, ColB varchar(1), ColC int)
insert into @U select 1, ColA, ColB, ColC from @t where ColA = @Root
declare @level int
set @level = 2
while not exists (select * from @U where ColC is null)
begin
insert into @U
select @level, ColA, ColB, ColC from @t
where ColA in (select ColC from @U where level = @level - 1)
set @level = @level + 1
end
select @Root as ColA, ColB from @U order by level
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I'm stuck with SQL7 at the moment, so many thanks for all your help.
Much appreciated.
Doug
-
just for the record, to tease a bit
SQL2005 way:
start with RyanRandall 's example ...
;
with MyCTE
as (
select @level as MyLevel, ColA, ColB, ColC
from @t
where ColA = @Root
UNION ALL
select @level, A.ColA, A.ColB, A.ColC
from @t A
inner JOIN MyCTE B
on A.ColA = B.ColC
)
--select *
select @Root as ColA, ColB
from MyCTE
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Wow! that's really neat. Since this is a fairly new project I'm certainly considering moving it up to SQL2005. Is there a cheap developer edition I can get to play around with and show my people how much better it would be?
Doug
-
maybe you'll find it overhere :
http://www.microsoft.com/sql/prodinfo/features/default.mspx
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for that. I'm trying to find out locally how to get a copy as I don't live in the States.
Hope you don't mind if I ask another question.
In your example, you built the table as part of the T-SQL, but in reality a much more complex table exists in my database which I need to get and then manipulate as you have shown and then output the data in a similar format to that shown.
If I select the data I need, how do I then access the data returned by the select?
For example:
Select ColA, ColB, ColC
From tableA
Where tableID in (listOfIDsRequestedByUser);
Now how do I get the value of say ColC in row 2 of the result set?
And if I do it by setting the select equal to some variable e.g. @resultset nvarchar(n), how do I know what size to set n as the result set could be any size at some unknown time in the future and then is the value I want equal to @resultset.ColC[2] or what?
Doug
-
So your TableA is dynamic ?
if not, write a query to perform your select, determine a maximum occurences for listOfIDsRequestedByUser and just build and inlist that supports it;
or build a #tempTb containing the tableID's you parsed from the inputparameter containing the id's.
this parsing can be done like this :
I copied this partial from http://www.sqlservercentral.com/scripts/contributions/592.asp
declare @tableIDList varchar(4000),
@Delimiter varchar(10)
-- for test
select @tableIDList = 'A,B,C', @Delimiter=','
--
create table #tblSplit
(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Item varchar(4000) NULL)
DECLARE @Delimiter2 varchar(12),
@item varchar(4000),
@iPos int,
@DelimWidth int
--had to do this cuz if they send in a > 9 char delimiter I could not pre and post append the % wildcards
SET @Delimiter2 = @Delimiter
SET @Delimiter2 = ISNULL(@Delimiter2, ',')
SET @DelimWidth = LEN(@Delimiter2)
IF RIGHT(RTRIM(@tableIDList), 1) <> @Delimiter2
SELECT @tableIDList = RTRIM(@tableIDList) + @Delimiter2
IF LEFT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = '%' + @Delimiter2
IF RIGHT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = @Delimiter2 + '%'
SELECT @iPos = PATINDEX(@Delimiter2, @tableIDList)
WHILE @iPos > 0
BEGIN
SELECT @item = LTRIM(RTRIM(LEFT(@tableIDList, @iPos - 1)))
IF @@ERROR <> 0 BREAK
SELECT @tableIDList = RIGHT(@tableIDList, LEN(@tableIDList) - (LEN(@item) + @DelimWidth))
IF @@ERROR <> 0 BREAK
INSERT INTO #tblSplit VALUES(@item)
IF @@ERROR <> 0 BREAK
SELECT @iPos = PATINDEX(@Delimiter2, @tableIDList)
IF @@ERROR <> 0 BREAK
END
select * from #tblSplit
Then you can join with #tblSplit
To get the value of colc in your result at row 2, you could
select @Root as ColA, colc , ColB from @U order by level, @Root, colc
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I really do thank you so much for all your help, but since I don't quite understand your last reply I think I may have misled you again in waht I'm trying to do.
This is what I actually need to do:
Get all rows from db tables a and b (with join) where a.ColA (PK) matches any in list of ColA's and other conditions.
Loop through rows {
--Add row to new table variable.
--While ColC in last added row not equal null {
----Get row from db table a where ColA = lastAddedRow.ColC
----Add data from table b and ColA from table a in lastAddedRow to new data from table a except ColA
----Add this new row to table variable
--}
}
Select table variable for output.
Now I've had a go at writing this and I get the error 'incorrect syntax near table'.
My code as follows:
SET NOCOUNT ON
DECLARE @TaxID int,
@PriceID int,
@Inclusive bit,
@ShowWithItem bit,
@ShowWithTotal bit,
@TaxName nvarchar(30),
@TaxRate smallmoney,
@TaxStart smalldatetime,
@TaxEnd smalldatetime,
@ReplacedBy int,
@lastReplacedBy int,
@t table (TaxID int, PriceID int, Inclusive bit, ShowWithItem bit, ShowWithTotal bit, TaxName nvarchar(30), TaxRate smallmoney, TaxStart smalldatetime, TaxEnd smalldatetime, ReplacedBy int)
DECLARE cTax CURSOR LOCAL FAST_FORWARD
FOR
SELECT Taxes.TaxID, PriceID, Inclusive, ShowWithItem, ShowWithTotal, TaxName, TaxRate, TaxStart, TaxEnd, ReplacedBy
FROM Taxes, TaxesApplied
WHERE //conditions
OPEN cTax
FETCH NEXT FROM cTax INTO @TaxID, @PriceID, @Inclusive, @ShowWithItem, @ShowWithTotal, @TaxName, @TaxRate, @TaxStart, @TaxEnd, @ReplacedBy
INSERT INTO @t (TaxID, PriceID, Inclusive, ShowWithItem, ShowWithTotal, TaxName, TaxRate, TaxStart, TaxEnd, ReplacedBy) VALUES (@TaxID, @PriceID, @Inclusive, @ShowWithItem, @ShowWithTotal, @TaxName, @TaxRate, @TaxStart, @TaxEnd, @ReplacedBy)
WHILE @@Fetch_Status = 0
BEGIN
WHILE @ReplacedBy NULL
SET @lastReplacedBy = @ReplacedBy
BEGIN
SELECT @TaxName = TaxName, @TaxRate = TaxRate, @TaxStart = TaxStart, @TaxEnd = TaxEnd, @ReplacedBy = ReplacedBy
FROM Taxes
WHERE TaxID = @lastReplacedBy
INSERT INTO @t (TaxID, PriceID, Inclusive, ShowWithItem, ShowWithTotal, TaxName, TaxRate, TaxStart, TaxEnd, ReplacedBy) VALUES (@TaxID, @PriceID, @Inclusive, @ShowWithItem, @ShowWithTotal, @TaxName, @TaxRate, @TaxStart, @TaxEnd, @ReplacedBy)
END
FETCH NEXT FROM cTax INTO @TaxID, @PriceID, @Inclusive, @ShowWithItem, @ShowWithTotal, @TaxName, @TaxRate, @TaxStart, @TaxEnd, @ReplacedBy
END
CLOSE cTax
DEALLOCATE cTAX
SELECT * FROM @t
SET NOCOUNT OFF;
Doug
-
With SQL7 you will have to use a #tempTB in stead of a declare @tb table !
It does not know tablevariables !
If you are only interested in the last replacementId, I would first select the keys for the Baserows (where ColA = yyy)
then build an explosion list for these keys using the loop techneque.
Remember to put your First ColA in the explosionlist so you can select RootColA, max(replacementid)
from #tmpExplosion
group by RootColA
Lets hope you've got a better way than max(id) to determine the most recent (e;g; a datetime or so).
I hope this gets you on track
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks so much for all your help.
The table variable and a few other minor bugs were the last errors to cure and now my very first piece of T-SQL works perfectly!!
Thanks again,
Doug
-
FYI
check out http://www.microsoft.com/sql/downloads/trial-software.mspx
for an 180-days trial version.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I called Microsoft's local office and they've put me in touch with their distributor who sells it for $55, just a little more that the US price. So I should have it installed in about 2 days time. Can't wait !!
When I've got it I'll try out your newer method !!
Thanks again,
Doug
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply