| Author |
 Topic  |
|
|
richdiaz99
Starting Member
22 Posts |
 Posted - 01/16/2013 : 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 CategoryName
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
I 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 =)
|
Edited by - richdiaz99 on 01/17/2013 11:30:05
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 01/16/2013 : 17:17:28
|
This is how I would go at it:SELECT
a.*,
LTRIM(b.data) AS SubscriberDetails
FROM
(
SELECT DISTINCT
SubscriberId,
SubscriberTitle,
Email,
FirstName,
LastName
FROM
Tbl
) AS a
CROSS APPLY
(
SELECT
' ' + b.ArticleTitle + ' ' + b.CategoryName
FROM
Tbl b
WHERE
b.SubscriberId = a.SubscriberId
ORDER BY
b.PK
FOR XML PATH('')
) b(data); |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 01/16/2013 : 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 SubscriberDetails
FROM
@Foo AS Source
ORDER BY
Source.SubscriberId |
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 01/17/2013 : 10:49:44
|
quote:
Originally posted by James K
This is how I would go at it:SELECT
a.*,
LTRIM(b.data) AS SubscriberDetails
FROM
(
SELECT DISTINCT
SubscriberId,
SubscriberTitle,
Email,
FirstName,
LastName
FROM
Tbl
) AS a
CROSS 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! 
|
Edited by - richdiaz99 on 01/17/2013 12:07:11 |
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 01/17/2013 : 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 SubscriberDetails
FROM
(
SELECT DISTINCT
SubscriberId,
SubscriberTitle,
Email,
FirstName,
LastName
FROM
@Foo
) AS a
CROSS APPLY
(
SELECT
' ' + ArticleTitle + ' ' + CategoryName
FROM
@Foo
WHERE
SubscriberId = a.SubscriberId
ORDER BY
PK
FOR XML PATH('')
) b(data);
|
Edited by - richdiaz99 on 01/17/2013 11:27:30 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 01/17/2013 : 11:24:57
|
| Great! Glad you got it working!! |
|
|
| |
Topic |
|
|
|
Stuck on SQL Select =/ (solved with CROSS APPLY)
