clear log: concat

Friday, March 9, 2012

how to concat/pivot rows to column?

hello,

I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.

Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...

thanks in advance!There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP|||Originally posted by Pat Phelan
There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP

PatP, thanks for your reply. After posting I realize I should have included more information.
Here's more specifics:
CREATE TABLE [elements] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[description] [varchar] (50) NULL ,
[code] [varchar] (5000) NOT NULL ,
[ord] [int] NOT NULL
) ON [PRIMARY]
GO

elements.code contains html tags, such as table, tr, td. I am using a stored procedure to build html code based on an input parameter. The parameter matches the 'name' column.
so to build a table, i would select the code and order by the ord column. the result is similar to the following:
<table width="100%" border="0">
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
(10 rows).
I would like to query the table based on the parameter passed to return the same results, except in one record:
<table width="100%" border="0"><tr><td></td><td></td><td></td></tr></table>
(1 row).

hope this helps clear it up|||That helps a bunch. The biggest problem that I see is that you can't allow your html table definition to exceed 4000 characters if you use 16 bit characters (aka UTF-8), or 8000 characters if you use 8 bit (OEM) characters. This could be a real problem for complex pages.

With that said, I'd start with:CREATE FUNCTION dbo.tableDef(@.name AS VARCHAR(50) RETURNS VARCHAR(8000) AS BEGIN
DECLARE
@.c VARCHAR(5000)
, @.r VARCHAR(8000)

SELECT @.r = ''
DECLARE z1 CURSOR FOR SELECT [code]
FROM [elements]
WHERE name = @.name
ORDER BY ord

OPEN z1
FETCH z1 INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z1 INTO @.c
END

CLOSE z1
DEALLOCATE z1

RETURN @.r
END-PatP|||Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP|||Originally posted by Pat Phelan
Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP

many thanks, Pat. i was hoping there was a 'simpler' method of reaching this goal. sometimes i wish i could rewrite ms's implementation of the ansi select to include special tricks.
like: select + * from blah would concat results. ;)

i'll let you know how it works, i'm not too worried about the 4/8k character limit, i can always have a couple of columns.

thanks again.

How to concat two xml into one xml ?

for example:
Xml1 = '<header>MyHeader</header>'
Xml2 = '<body><line>1</line><line>2</line></body>'
I want concat two xml into one xml as:
XmlResult =
'<header>MyHeader</header><body><line>1</line><line>2</line></body>'
How to concat two xmls in the sql server 2005?
You can simply cast it to varchar(max)/nvarchar(max), concatenate and then
cast it back.
declare @.x xml, @.y xml
select @.x = '<a>aaa</a>', @.y='<b>bbb</b>'
select cast(cast(@.x as nvarchar(max)) + cast(@.y as nvarchar(max)) as xml)
Uytkownik "ABC" <abc@.abc.com> napisa w wiadomoci
news:OOZYetDyHHA.600@.TK2MSFTNGP05.phx.gbl...
> for example:
> Xml1 = '<header>MyHeader</header>'
> Xml2 = '<body><line>1</line><line>2</line></body>'
> I want concat two xml into one xml as:
> XmlResult =
> '<header>MyHeader</header><body><line>1</line><line>2</line></body>'
> How to concat two xmls in the sql server 2005?
>
|||Pawel's method works really well but from what I remember you can also use FOR
XML.
For example, if you want to concatenate two XML variables @.x and @.y you cna do
something like
SELECT @.x, @.y FOR XML PATH ('')
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:ebAuNkFyHHA.5980@.TK2MSFTNGP04.phx.gbl...
> You can simply cast it to varchar(max)/nvarchar(max), concatenate and then
> cast it back.
> declare @.x xml, @.y xml
> select @.x = '<a>aaa</a>', @.y='<b>bbb</b>'
> select cast(cast(@.x as nvarchar(max)) + cast(@.y as nvarchar(max)) as xml)
> Uytkownik "ABC" <abc@.abc.com> napisa w wiadomoci
> news:OOZYetDyHHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||Denis,
Your solution is pretty cool and much shorter than mine. I would prefer
yours.
Thx
Regards
Pawel Potasinski
Uytkownik "Denis Ruckebusch [MSFT]" <denisruc@.online.microsoft.com> napisa
w wiadomoci news:469d55c2$1@.news.microsoft.com...
> Pawel's method works really well but from what I remember you can also use
> FOR XML.
> For example, if you want to concatenate two XML variables @.x and @.y you
> cna do something like
> SELECT @.x, @.y FOR XML PATH ('')
>
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:ebAuNkFyHHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Friday, March 9, 2012

how to concat/pivot rows to column?

How to concat two xml into one xml ?

clear log

Blog Archive

About Me