Showing posts with label single. Show all posts
Showing posts with label single. Show all posts

Monday, March 26, 2012

Retuning rows as a single string

Is there any way to do this?
Say I have SELECT MyCol FROM MyTable
MyCol is a nvarchar field, but instead of coming back like this in a table
ItemA
ItemB
ItemC
I want it to come back as a stingle string like this
ItemA, ItemB, ItemC
is this possible in T-SQL? thanks!http://www.aspfaq.com/show.asp?id=2529

> Is there any way to do this?
> Say I have SELECT MyCol FROM MyTable
> MyCol is a nvarchar field, but instead of coming back like this in a table
> ItemA
> ItemB
> ItemC
> I want it to come back as a stingle string like this
> ItemA, ItemB, ItemC|||Try this:
Declare @.Results varchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + CAST(MyCol AS
varchar(3))
FROM
MyTable
Select @.Results
HTH
Barry|||Sorry that should read:
Declare @.Results nvarchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + MyCol
FROM
MyTable
Select @.Results|||didn't know about coalesce, thanks!
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1145990261.449558.38970@.y43g2000cwc.googlegroups.com...
> Sorry that should read:
> Declare @.Results nvarchar(2000)
>
> SELECT @.Results =
> COALESCE(@.Results + ', ', '') + MyCol
> FROM
> MyTable
>
> Select @.Results
>
Posted by at No comments:
Labels: , , , , , , , , , , , , , , , , ,

Retriving data with same ID into single row

Hi,
I have a problem with retriving data from one table.
Table looks like this:
ID CONTACT EMPLOYEE_ID
And now, one employee can how more than one contact, like this
ID CONTACT EMPLOYEE_ID
15 e-mail 553
16 phone 553
..and so on.
How can I retrive this contacts, for same employee into single row, and divi
ded with 'coma'?
Now a can retrive them but I recieve multiple rows?
Thanks for helpHi
Do such reports on the client side.
It you want to do that by T-SQL be aware that it is not relyable solution
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"s3v3n" <s3v3n.1yuclt@.mail.codecomments.com> wrote in message
news:s3v3n.1yuclt@.mail.codecomments.com...
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ..and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||Check this good article with source code "Returning a Comma-Delimited List o
f
Related Records"
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||[Based on the 4guysrolla article .. ]
Create table empTest
(
[ID] int identity,
Contact varchar(100),
Employee_Id int
)
Go
Insert into empTest values ( 'abc@.email.com', 554)
Insert into empTest values ( '090909090', 554)
Go
Create function dbo.udf_GetEmpDetails(@.EmpID int)
Returns Varchar(1000) as
BEGIN
DECLARE @.Contact varchar(1000)
SELECT @.Contact = COALESCE(@.Contact + ', ', '') + s.Contact
FROM empTest s
WHERE s.EMPLOYEE_ID = @.EmpID
RETURN @.Contact
END
Go
Select distinct Employee_ID,
dbo.udf_GetEmpDetails(EMPLOYEE_ID) as ListOfContacts
From empTest f
Go
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:

> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>
Posted by at No comments:
Labels: , , , , , , , , , , , , ,

retrieving XML issues - SQL 2000

In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.
Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com@.207.46.24 8.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
sql
Posted by at No comments:
Labels: , , , , , , , , , , , , ,

retrieving XML issues - SQL 2000

In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com
@.207.46.248.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
Posted by at No comments:
Labels: , , , , , , , , , , , , ,

Tuesday, March 20, 2012

Retrieving Hierarchical Data from a single table

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.

Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure.
Example data:

ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level
-------------------------------------------
1 Custom Furniture 0 0
2 Boxes 0 0
3 Toys 0 0
4 Bedroom 1 Custom Furniture 1
5 Dining 1 Custom Furniture 1
6 Accessories 1 Custom Furniture 1
7 Picture Frames 6 Accessories 2
8 Serving Trays 6 Accessories 2
9 Entertainment 1 Custom Furniture 1
10 Planes 3 Toys 1
11 Trains 3 Toys 1
12 Boats 3 Toys 1
13 Automobiles 3 Toys 1
14 Jewelry 2 Boxes 1
15 Keepsake 2 Boxes 1
16 Specialty 2 Boxes 1

Desired output:

Custom Furniture
Accessories
Picture Frames
Serving Trays
Bedroom
Dining
Entertainment
Boxes
Jewelry
Keepsake
Specialty
Toys
Automobiles
Boats
Planes
Trains

Hello, if I get you right, here is a very short article that might get you started:http://www.mmkit.com/article.php?sid=345&lang=en_GB

HTH. -LV

|||The answer very much depends on whether you are using Sql Server 2005 or an older version. Assuming 2005 then have a read ofHeirarchical Queries in Sql Server 2005.|||Thanks much. This was exactly what I needed. I don't think I could have come up with this solution on my on at this point.|||

--Copy the code to run on your machine. I named your table as TreeSource with columns needed.

IF(SELECTOBJECT_ID('TreeSource','U'))ISNOTNULL

BEGIN

DROPTABLE TreeSource

END

GO

CREATETABLE [dbo].[TreeSource](

[ProductCategoryID] [int]NULL,

[CatDescription] [nvarchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentProductCategoryID] [int]NULL

)ON [PRIMARY]

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(1,'Custom Furniture',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(2,'Boxes',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(3,'Toys',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(4,'Bedroom',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(5,'Dining',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(6,'Accessories',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(7,'Picture Frames',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(8,'Serving Trays',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(9,'Entertainment',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(10,'Planes',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(11,'Trains',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(12,'Boats',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(13,'Automobiles',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(14,'Jewelry',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(15,'Keepsake',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(16,'Specialty',2)

--step 0

CREATETABLE #Tree(

NodeintNOTNULLIDENTITY(100, 1),

ParentNodeint,

ProductCategoryIDintNOTNULL,

Depthtinyint,

Lineagevarchar(50))

--step 1

INSERTINTO #Tree(ProductCategoryID)SELECT ProductCategoryIDFROM TreeSourceORDERBY ParentProductCategoryID,CatDescription

Go

UPDATE TSET T.ParentNode=P.Node

FROM dbo.#Tree T

INNERJOIN TreeSource EON T.ProductCategoryID=E.ProductCategoryID

INNERJOIN TreeSource BON E.ParentProductCategoryID=B.ProductCategoryID

INNERJOIN dbo.#Tree PON B.ProductCategoryID=P.ProductCategoryID

GO

--step 3

UPDATE #TreeSET Lineage='.', Depth=0WHERE ParentNodeIsNull

--step 4

WHILEEXISTS(SELECT*FROM #TreeWHERE DepthIsNull)

UPDATE TSET T.depth= P.Depth+ 1,

T.Lineage= P.Lineage+Ltrim(Str(T.ParentNode,4,0))+'.'

FROM #TreeAS T

INNERJOIN #TreeAS PON(T.ParentNode=P.Node)

WHERE P.Depth>=0

AND P.LineageIsNotNull

AND T.DepthIsNull

--step 5 final

SELECTSpace(T.Depth*4)+ E.CatDescriptionASName

FROM TreeSource E

INNERJOIN #Tree TON E.ProductCategoryID=T.ProductCategoryID

ORDERBY T.Lineage+Ltrim(Str(T.Node,6,0))

--SELECT * FROM #Tree

DROPTable #Tree

--read more http://www.sqlteam.com/item.asp?ItemID=8866

Posted by at No comments:
Labels: , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)