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

avatar 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

avatar image

Kev Riley ♦♦
64k 48 61 81

+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:

x2077

asked: Jun 28, 2012 at 09:55 AM

Seen: 877 times

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

Copyright 2016 Redgate Software. Privacy Policy