vinu512 (12/31/2012)

---

Satish's Query is partially correct but it doesn't break the records on th basis of Column1. You would nee to do a Correlated Subquery for that as follows:

--Creating Table 

create table #test
( column1 varchar(100),
column2 varchar(100),
column3 varchar(100)
)


--Inserting Sample Data

insert into #test
select 'red' as Column1, 'shirt' as Column2, 'for women' as Column3 union all
select 'green', 'jumper', 'for men' union all
select 'yellow', 'blouse', 'for girls' union all
select 'black', 'coat', 'extra large' 


--Query 

Select STUFF
	(
		(select DISTINCT ',' + A.column1 + ' ' + B.column2 + ' ' + C.column3 from #test As A cross join #test As B 
				cross join #test As C Where A.column1 = p.column1 For XML Path('')),1,1,''
	) 
From #test As p 

Hope this is what you are looking for.

SELECT STUFF((
    SELECT ',' + a.Column1 + ' ' + MyString  
    FROM (
        SELECT MyString= a.Column2 + ' ' + b.Column3
        FROM #test a CROSS JOIN #test b) b
    FOR XML PATH('')), 1, 1, '')
FROM #Test a

dwain.c (1/1/2013)

---

Hey Vinu! Happy New Year!

I think if you don't do 2 CROSS JOINs you can eliminate the need for DISTINCT as follows:

SELECT STUFF((
    SELECT ',' + a.Column1 + ' ' + MyString  
    FROM (
        SELECT MyString= a.Column2 + ' ' + b.Column3
        FROM #test a CROSS JOIN #test b) b
    FOR XML PATH('')), 1, 1, '')
FROM #Test a

Uses your test data setup.

VegasL (1/5/2013)

---

Thanks Jeff for englightening me on the benefit to the forum members of using create table / select/insert statements for readily consumable data. This is all new to me in fact using those statement, but I think from the examples from the other forum members I understand this now, so hopefully going forward, I will use them more effectively when posting to the forum.

Thanks vinu512 for posting the initial solution, along with showing me the correct way to use the create table function.

Thanks Dwain for posting you're solution, as I believe this is what I was looking for.

Few Questions, Is Stuff a valid tsql function? As I didn't see that in the insert / create table function that Vinu created. So not clear how that is working?

Also when creating this tables, I noticed these tables where being generated in the system db , tempdb, under master., under temporary tables.


I assume its safe to delete these temporary tables under master?

Lastly, going forward, when requesting help when creating tables, any way to assign the this to a temporary db i create of my own so it doesn't put into master. maybe something like

use mytempdb
create table #test



Thanks ALL. You're help is most appreciated

VegasL (1/5/2013)

---

Thanks vinu512 for posting the initial solution, along with showing me the correct way to use the create table function.