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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 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

3873 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

3873 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  
 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.12 seconds. Powered By: Snitz Forums 2000