SQL Query Writing Help

Question

SQL Query Writing Help

ajosh k john

Valued Member

Points: 69
More actions
November 20, 2005 at 11:13 pm

#88563

Hi,
I want help from u people to write a query.
Let me to describe my need.I have a function with retives some numbers seperated by comma.Based on numbers retrived i am able to select the description of that paticualr number from a master table.I need to create a table based on this discriptions ,if say some time the function retrives 3 or some times 4 like this(1,2,3 or 4,5,6,7).
my query is like this to take the description
select @Name=Description from <TableName> where Unit_ID=@Unit_Id
This @Unit_ID will retive on itration
i want to create a table based on the @name
Can any one help me to sole this...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you please post the definition of your table, some example data and expected output please. I'm not completely sure from your description what it is you want.

thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ajosh k john Valued Member Points: 69 More actions · Answer 2

i have a master table that have some unit name based on unit id.Using a function named unitbelow i pass one Parentunitid and i get the corresponding unitid under that parent UnitID in comma separated string make an itration i have to make a table based on the unitid string if the unitID retrives 1,2,3,4 .I have to take take the unit name from the unit master table and make a table based in it,this table have 4 coloums .If the unitbelow function retrives 1,2,3 the table have 3 coloums,like that ...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Does the function have to return a string. It would be easier if it was a table-returning function.

If it has to be a string, you could always count the nuber of commas in the string, add one and use that to create the correct number of coulmns.

What's this table with variable number of columns going to store? The returned IDs?

As for posting the schema, please have a look at this article, http://www.aspfaq.com/etiquette.asp?id=5006

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ajosh k john Valued Member Points: 69 More actions · Answer 4

This is for the creation of a dynamic report.I can give my code to generate my table

create table kpuser.tbl(vtype varchar(50))

declare @UnitID varchar(8000),@FieldName varchar(50),@Unit_ID varchar(30)

select @UnitID=dbo.unitbelow('252')

set @UnitID = @UnitID + ','

print @UnitID

Declare @Flag int

declare @delimiter nchar(1)--

set @delimiter = ','

set @Flag=0

DECLARE @pos int

DECLARE @tmpval varchar(8000),@last varchar(50)

SET @pos = charindex(@delimiter, @UnitID)

WHILE @pos > 0

BEGIN

SET @tmpval = left(@UnitID, charindex(@delimiter, @UnitID) - 1)

SET @Unit_ID= @tmpval

select @FieldName=Unit_Name from tbl_Unit_Master where Unit_ID=@Unit_ID

alter table kpuser.tbl add @FieldName varchar(50)

SET @UnitID = substring(@UnitID, @pos + 1, len(@UnitID))

SET @pos = charindex(@delimiter, @UnitID)

end