x

Store Column Result in a Table Cell

Table Structure

Id     Name     Url

1        Test1     http://Dummy1

2        Test1     http://Dummy2

3        Test2      http://Dummy1

My Query :- "**If user Searches for Url suppose [htttp://Dummy1] then display new column in result which will display all the Names associated with Url separated by '~'** "

I want result in this Manner:-

Id      Name       Url             Assign

1        Test1   http://Dummy1  ~Test1~Test2~

2        Test1   http://Dummy2  ~Test1~

3        Test2   http://Dummy1  ~Test1~Test2~

Thanks in Advance.
more ▼

asked Jun 28, 2012 at 09:55 AM in Default

AdI_23 gravatar image

AdI_23
0 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
if object_id('Yourtable', 'U') is not null drop table YourTable

create table YourTable (id int,name varchar(10),URL varchar(100))
insert into YourTable select 1,'Test1','http://Dummy1'
insert into YourTable select 2,'Test1','http://Dummy2'
insert into YourTable select 3,'Test2','http://Dummy1'


select
     YourTable.ID,
     YourTable.Name,
     YourTable.URL,
     AllNames.Assign
from
     YourTable
 join
 (
   select  
      distinct
      URL ,
      ( select '~' + name
        from YourTable YT1
        where YT1.URL = YT2.URL
        for xml path('')
      )
      +'~' as Assign
   from
      YourTable YT2
 )AllNames on YourTable.URL = AllNames.URL
order by ID

gives you

ID          Name       URL             Assign
----------- ---------- --------------- ---------------
1           Test1      http://Dummy1   ~Test1~Test2~
2           Test1      http://Dummy2   ~Test1~
3           Test2      http://Dummy1   ~Test1~Test2~
(3 row(s) affected)
more ▼

answered Jun 28, 2012 at 10:13 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

+1. Creating a non-XML, separated string out of a bunch of rows is more or less the only thing I ever use the XML-capabilities in SQL Server for...
Jun 28, 2012 at 01:23 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853

asked: Jun 28, 2012 at 09:55 AM

Seen: 781 times

Last Updated: Jun 28, 2012 at 01:23 PM