Author |
Topic |
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-16 : 16:29:17
|
Totally Stumped =/I think I am overthinking this, I've been messing with cursors and parsing logic for hours but I can not get this to work =/Here's the deal: I have the following table of 4 rows:Pk SubscriberId SubscriberTitle Email FirstName LastName ArticleTitle CategoryName1 1 Mr. test@test.com Rich Smith This is article ONE News Releases2 1 Mr. test@test.com Rich Smith This is article TWO News Releases3 1 Mr. test@test.com Rich Smith This is article THREE News Releases4 2 Mr. test@test.com Larry Mills This is an article ONE News ReleasesI need the rows for each unique SubscriberId combined into a NEW table (will actually be the return value of a stored proc), so in this instance it will be a 2 row result table since there are only two unique SubscriberId's. The format of the new table is simply to reuse columns 2-4 (i.e not Pk) and just concatenate ALL the "ArticleTitle" and "CategoryName" columns for each SubscriberId's row into a new column: "SubscriberDetails".That result table would look like this:Pk SubscriberId SubscriberTitle Email FirstName LastName SubscriberDetails (i.e. the combined results)1 1 Mr. test@test.com Rich Smith "This is article ONE News Releases This is article TWO News Releases This is article THREE News Releases"2 2 Mr. test@test.com Larry Mills "This is an article ONE News Releases"Sorry for the lousy table formatting, I don't see anywhere on the forum to make a grid =/ I see an * HTML is OFF but I have no idea how to turn it on.So, should this be done with Cursors or some kind of Select Into TempTable? I'm lost =/Please comment or point me to an article that covers how to do this.Many thanks for any help =) |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-16 : 17:17:28
|
This is how I would go at it:SELECT a.*, LTRIM(b.data) AS SubscriberDetailsFROM( SELECT DISTINCT SubscriberId, SubscriberTitle, Email, FirstName, LastName FROM Tbl) AS aCROSS APPLY( SELECT ' ' + b.ArticleTitle + ' ' + b.CategoryName FROM Tbl b WHERE b.SubscriberId = a.SubscriberId ORDER BY b.PK FOR XML PATH('')) b(data); |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-16 : 17:22:21
|
Maybe this will help get you started:DECLARE @Foo TABLE ( Pk INT, SubscriberId INT, SubscriberTitle VARCHAR(4), Email VARCHAR(255), FirstName VARCHAR(50), LastName VARCHAR(50), ArticleTitle VARCHAR(2000), CategoryName VARCHAR(255))INSERT @Foo VALUES (1, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article ONE', 'News Releases'), (2, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article TWO', 'News Releases'), (3, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article THREE', 'News Releases'), (4, 2, 'Mr.', 'test@test.com', 'Larry', 'Mills', 'This is an article ONE', 'News Releases')SELECT DISTINCT Source.SubscriberId, STUFF((SELECT DISTINCT TOP 100 PERCENT ' ' + T1.ArticleTitle + ' ' + T1.Categoryname FROM @Foo AS T1 WHERE T1.SubscriberId = Source.SubscriberId ORDER BY ' ' + T1.ArticleTitle + ' ' + T1.Categoryname FOR XML PATH('')), 1, 1, '') AS SubscriberDetailsFROM @Foo AS SourceORDER BY Source.SubscriberId |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-17 : 10:49:44
|
quote: Originally posted by James K This is how I would go at it:SELECT a.*, LTRIM(b.data) AS SubscriberDetailsFROM( SELECT DISTINCT SubscriberId, SubscriberTitle, Email, FirstName, LastName FROM Tbl) AS aCROSS APPLY( SELECT ' ' + b.ArticleTitle + ' ' + b.CategoryName FROM Tbl b WHERE b.SubscriberId = a.SubscriberId ORDER BY b.PK FOR XML PATH('')) b(data);
You are AWESOME James, I got it to work Your code is crazy efficient. Mine was, well lets say no one will ever see it I will have to Google "CROSS APPLY"I will post up a working final solution in a few minutes.MANY THANKS! |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-01-17 : 11:03:50
|
The below is the working script, thanks again! (I did have to make a four small changes to the suggested code above, in bold below)--I need the rows for each unique SubscriberId combined into a NEW table, so in this instance it will be a 2 row result table since there are only two unique SubscriberId's. --The format of the new table is simply to reuse columns 2-4 (i.e not Pk) and just concatenate ALL the "ArticleTitle" and "CategoryName" columns for each SubscriberId's row into a new column: "SubscriberDetails".DECLARE @Foo TABLE ( Pk INT, SubscriberId INT, SubscriberTitle VARCHAR(4), Email VARCHAR(255), FirstName VARCHAR(50), LastName VARCHAR(50), ArticleTitle VARCHAR(2000), CategoryName VARCHAR(255))INSERT @Foo VALUES (1, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article ONE', 'News Releases'), (2, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article TWO', 'News Releases'), (3, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article THREE', 'Industry News & Market Reports'), (4, 2, 'Mr.', 'test@test.com', 'Larry', 'Mills', 'This is an article ONE', 'News Releases')SELECT a.*, LTRIM(b.data) AS SubscriberDetailsFROM( SELECT DISTINCT SubscriberId, SubscriberTitle, Email, FirstName, LastName FROM @Foo) AS aCROSS APPLY( SELECT ' ' + ArticleTitle + ' ' + CategoryName FROM @Foo WHERE SubscriberId = a.SubscriberId ORDER BY PK FOR XML PATH('')) b(data); |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-17 : 11:24:57
|
Great! Glad you got it working!! |
|
|
|
|
|