SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stuck on SQL Select =/ (solved with CROSS APPLY)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richdiaz99
Starting Member

22 Posts

Posted - 01/16/2013 :  16:29:17  Show Profile  Reply with Quote
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

3761 Posts

Posted - 01/16/2013 :  17:17:28  Show Profile  Reply with Quote
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);
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/16/2013 :  17:22:21  Show Profile  Reply with Quote
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
Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 01/17/2013 :  10:49:44  Show Profile  Reply with Quote
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
Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 01/17/2013 :  11:03:50  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 01/17/2013 :  11:24:57  Show Profile  Reply with Quote
Great! Glad you got it working!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000