December 28, 2012 at 7:19 pm
Hello,
Is this possible?
I have several columns, such as:
Coulmn A
red
green
yellow
black
Column B
shirt
jumper
blouse
coat
Column C
for women
for men
for girls
extra large
I have permutated the columns in such a way that it gives me the following result:
red shirt for women
red shirt for men
red shirt for girls
red shirt extra large
....
black coat for girls
black coat extra large
using a cross join.
Now what I would like to do, if possible, is take all the results where it reads,
red shirt for women, red shirt for men, red shirt for girls, red shirt exta large
green shirt for women, green shirt for men, green shirt for girls, green shirt exta large
...
black coat for women, black coat for men, black coat for girls, black coat extra large
basically where it groups then by column a, which is the main word(s). by transposing the cross joined words and put them on the same row, contactenated with commas.
hopefully this makes sense?
Thanks
December 28, 2012 at 9:37 pm
I suspect the reason why you didn't get much help on your last similar post is because people don't have the time to setup the test data. Please see the article at the first link in my signature line below for how to post data in a readily consumable format. Once you've done that, post your Cross Join code with the readily consumable data and I'm sure that someone will jump up to help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2012 at 9:59 pm
Here is the code
if OBJECT_ID('tempdb..#test') is not null drop table #test
go
create table #test
( column1 varchar(100),
column2 varchar(100),
column3 varchar(100)
)
go
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'
go
declare @Result varchar(4000) = ''
select @Result = @Result + A.column1 + space(1) + B.column2 + space(1) + C.column3 + ', '
from #test A
cross join #test B
cross join #test C
select left(@Result,LEN(ltrim(rtrim(@Result)))-1) as FinalResult
if OBJECT_ID('tempdb..#test') is not null drop table #test
go
December 31, 2012 at 3:29 am
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.
January 1, 2013 at 7:37 pm
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.
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 1, 2013 at 10:17 pm
dwain.c (1/1/2013)
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.
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.
Hi Dwain,
Happy New Year!!!
Yes you are right Dwain.....I didn't go that deep into the solution. I just transformed the solution given by SatishAiyyar using STUFF.
January 5, 2013 at 1:22 pm
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
January 6, 2013 at 9:32 am
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
Hmmm... it sounds like you're not just really new this forum but maybe really new to the world of SQL itself. Here's a hint that really helped me when I was first starting out.
One of your best friends in the world should be a thing called "Books OnLine" (abbreviated as simply BOL). You can easily get there from SSMS just by pressing the {f1} key. Then you can lookup things like what "STUFF" actually is (yes, it's a real function) and more information on temporary tables and where they're actually created at no matter what the current database is. Even with over 16 years of experience, I still use BOL several times a day if, for nothing else, just to make sure of things.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2013 at 2:08 am
VegasL (1/5/2013)
Thanks vinu512 for posting the initial solution, along with showing me the correct way to use the create table function.
I'm glad I was of some help.
You should also thank SSC for making it possible for you to get advice from people like Jeff and a lot more other people who devote a lot of their time to this forum. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply