consolidate columns - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

consolidate columns

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-24 : 10:33:48

I have the following two table

TBL1:
columns: ID (pk), NAMES, YEARS

TBL2:
columns: tbl1_ID, no

with a one-to-many relationship between TBL1 to TBL2.
Each combination of names and years is a unique record in TBL1 and has multiple matches in TBL2.

The result set that I need is as follows:
I would like to combine all records that have the same 'NAMES' into one column with the various 'YEARS' values:
for example:
NAMES YEARS
FDR 234 XT 2001
FDR 234 XT 2003
FDR 234 XT 2004
FDR 234 XT 2005

Result: FDR 234 XT 2001,2003,2004,2005

Then I'd like to add all the matches of TBL2 to this record, like:

TBL1.[NAMES-YEARS] TBL2.[NO]
FDR 234 XT 2001,2003,2004,2005 123-A
FDR 234 XT 2001,2003,2004,2005 123-B

.....

Any help is appreciated!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 10:41:01

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-24 : 11:01:02

Thank you - that takes care of the first part of my problem.
Now that I have the NAMES/YEARS entries consolidated how to I match them with TBL2, since I don't have the unique ID available.
I feel like I'm missing a very simple step.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:57:47

whats the no column in tbl2 made of?

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

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-25 : 12:53:25

no column is an nvarchar field.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-25 : 13:24:26

Do you want to store your data as a delimited list? If so, why? That seems like a really bad idea if that is your intent.

http://en.wikipedia.org/wiki/Database_normalization

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-25 : 13:31:27

My intent is to save this data to an Excel file that is being used in brochure printing.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-25 : 14:15:58

[code]SELECT DISTINCT
t3.NAMES + ' ' + STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + CAST(t2.YEARS AS VARCHAR(4)) FROM TBL1 AS t2 WHERE t2.ID = t1.tbl1_ID ORDER BY ', ' + CAST(t2.YEARS AS VARCHAR(4)) FOR XML PATH('')), 1, 1, '') AS [NAMES-YEARS],
No
FROM
TBL2 AS t1
INNER JOIN
TBL1 AS t3
ON t1.tbl1_ID = t3.ID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources