Trying to make sense of cross linked spread sheet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to make sense of cross linked spread sheet

Author

Topic

InNomina
Starting Member

40 Posts

Posted - 2012-06-05 : 18:46:16

I have 2 spread sheets that I have imported.

They have a "link id" that associates records to keywords as there may be manyn keywords to 1 record id.

The problem is there are over 16000 of them and I need to sort them out so the end result is 1 column with the record id and the 2nd column with all the corresponding keywords to that record.

now...

I have imported both spread sheets to their own tables each with the 2 necessary columns filled with the data from each.

I can see all the link associations if I do a

select * from table1 inner join table2 on table1.link = table2.link

my results...

000015 31 SIMCOE 31
000015 32 COUNTY 32
000015 33 BY-LAW 33
000015 34 SOLID 34
000015 35 WASTE 35
000015 36 MANAGEMENT 36
000015 37 POWERS 37
000019 50 CANADA 50
000019 22 MUNICIPAL 22
000019 51 CONSENTS 51
000020 52 PROPOSED 52
000020 53 UHTHOFF 53
000021 45 SILVER 45
000021 46 CREEK 46
000021 47 MOBILE 47

What I need is an output into 2 columns that look like this...

Example:

000015 SIMCOE, COUNTY, BY-LAW, SOLID, WASTE,
000019 CANADA, CONSENTS
000020 PROPOSED, UHTHOFF
000021 SILVER, CREEK, MOBILE

-------------------------
"If you never fail, you're not trying hard enough"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 19:11:17

[code]
select * into #temp
from table1
inner join table2 on table1.link = table2.link

select field1,
stuff((select ',' + field3 from #temp where field1 = t.field1 order by field4 for xml path('')),1,1,'') AS List
from (select distinct field1 from #temp)t

drop table #temp

[/code]

i've just put field1, field2 etc as i dont know actual column names. Please put appropriate names in actual query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InNomina
Starting Member

40 Posts

Posted - 2012-06-06 : 10:59:38

Thank you, I will try it and post the results!

quote:
Originally posted by visakh16

select * into #temp
from table1
inner join table2 on table1.link = table2.link

select field1,
stuff((select ',' + field3 from #temp where field1 = t.field1 order by field4 for xml path('')),1,1,'') AS List
from (select distinct field1 from #temp)t

drop table #temp

i've just put field1, field2 etc as i dont know actual column names. Please put appropriate names in actual query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

-------------------------
"If you never fail, you're not trying hard enough"

InNomina
Starting Member

40 Posts

Posted - 2012-06-06 : 11:21:42

Here is what I ran...

select * into #temp
from keyfile
inner join keywords on keyfile.link = keywords.link

select Perm_no,
stuff((select ',' + keyword from #temp where perm_no = t.Perm_no order by perm_no for xml path('')),1,1,'') AS List
from (select distinct perm_no from #temp)t

drop table #temp

This is what it returned....

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'link' in table '#temp' is specified more than once.

-------------------------
"If you never fail, you're not trying hard enough"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-06 : 12:05:42

select * into #temp
from keyfile
inner join keywords on keyfile.link = keywords.link

You need to name and possibly alias column names instead of using *.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:33:25

quote:
Originally posted by InNomina

Here is what I ran...

select * into #temp
from keyfile
inner join keywords on keyfile.link = keywords.link

select Perm_no,
stuff((select ',' + keyword from #temp where perm_no = t.Perm_no order by perm_no for xml path('')),1,1,'') AS List
from (select distinct perm_no from #temp)t

drop table #temp

This is what it returned....

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'link' in table '#temp' is specified more than once.

-------------------------
"If you never fail, you're not trying hard enough"

replace * in first table with actual column names
and in case you've same column names in both the tables then rename them using different alias

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources