asked on

How do I create a crosstab query that will display grandchild records on SQL Server 2005?

I have been banging my head against the wall trying to figure out a query that will retrieve data from a database in a particular way.
I have a database within which are 3 tables: CUD, CU and C

CUD
cudID (PK) int
definition varchar(50)

CU
cuID (PK) int
cudID int (related to CUD)
subdefinition varchar(50)

C
cID (PK) int
cuID int (related to CU)
corder int
contenttext varchar(1000)

The CUD table holds content unit definitions, for example it might have a record:

cudID      definition
1      breaking news stories

Because breaking news stories contain multiple elements in every story, CU (content units) has the following records:

cuID      cudID      subdefinition
1      1      Title
2      1      Teaser
3      1      Story

The content that the preceding tables describe is in C. So if we can imagine that there are 2 news stories, C might have these records:

cID      cuID      Corder      ContentText
1      1      1      Story 1 title
2      2      1      Story 1 teaser
3      3      1      Story 1 body
4      1      2      Story 2 title
5      2      2      Story 2 teaser
6      3      2      Story 2 body

I am able to retrieve a list of story titles of breaking news stories easily enough with the following query:

SELECT Corder, ContentText
FROM C
WHERE cuID = (SELECT MIN(cuID) FROM CU WHERE cudID = 1) ORDER BY Corder

This will give me the following results:

Corder      ContentText
1      Story 1 Title
2      Story 2 Title

I can also retrieve all the information for a particular news story (for example story 2) with the following query:

SELECT TOP (1)
(SELECT Body FROM [C] AS C1 WHERE (Corder = 2) AND (cuID = 1)) AS [Title],
(SELECT Body FROM [C] AS C2 WHERE (Corder = 2) AND (cuID = 2)) AS [Teaser],
(SELECT Body FROM [C] AS C3 WHERE (Corder = 2) AND (cuID = 3)) AS [Story],
FROM [content] As C0

I can return all breaking news records with this query:

SELECT ContentText from C where cuID in (SELECT cuID FROM cu WHERE cudID = 1)

The problem is that it returns each field as a new record:

Story 1 title
Story 1 teaser
Story 1 body
Story 2 title
Story 2 teaser
Story 2 body

What I would like is to have one single query that output the group of repeating items as a single record so that the result looks like this:

Title            Teaser            Story
Story 1 title      Story 1 teaser      Story 1 body
Story 2 title      Story 2 teaser      Story 2 body

I have looked crosstab queries and PIVOT but I just cannot figure out a query that can do it.

Any ideas?

Thanks,
Paul.

reb73

Try -

SELECT	CUD.Definition
	,MAX(CASE WHEN C.CUId = 1 THEN BODY ELSE '' END) AS [Title]
	,MAX(CASE WHEN C.CUId = 2 THEN BODY ELSE '' END) AS [Teaser]
	,MAX(CASE WHEN C.CUId = 3 THEN BODY ELSE '' END) AS [Story]
FROM C
INNER JOIN CU ON CU.CUId = C.CUId
INNER JOIN CUD ON CUD.CUDId = CU.CUDId
GROUP BY CUD.Definition

Open in new window

Chris Luttrell

The above will work but you want ContentText in there like below.
It can be done with a pivot table also, not sure which is more efficent yet.

SELECT	CUD.Definition
	,MAX(CASE WHEN C.CUId = 1 THEN ContentText ELSE '' END) AS [Title]
	,MAX(CASE WHEN C.CUId = 2 THEN ContentText ELSE '' END) AS [Teaser]
	,MAX(CASE WHEN C.CUId = 3 THEN ContentText ELSE '' END) AS [Story]
FROM C
INNER JOIN CU ON CU.CUId = C.CUId
INNER JOIN CUD ON CUD.CUDId = CU.CUDId
GROUP BY CUD.Definition

Open in new window

pmodiano1

ASKER

Sorry, I made a mistake: C.ContentText is an nText field. When I try your queried (reb73 and CGLuttrell) I get the following error:

Msg 8117, Level 16, State 1, Line 1
Operand data type ntext is invalid for max operator.

Any ideas?

reb73

Oh yes, Aggregrate functions doe not work with unicode datatypes..

Actually, one of the queries in your original question can be slightly tweaked to give you what you need..

SELECT CUD.Definition
,(SELECT ContentText FROM C WHERE COrder = CUD.CUdId AND CUId = 1) AS [Title]
,(SELECT ContentText FROM C WHERE COrder = CUD.CUdId AND CUId = 2) AS [Teaser]
,(SELECT ContentText FROM C WHERE COrder = CUD.CUdId AND CUId = 3) AS [Story]
FROM CUD

Chris Luttrell

How big is the ContentText field you expect here? If it is under the 4000 character limit of NVARCHAR then this PIVOT will work:

SELECT Definition, [Title],[Teaser],[Story]
FROM
(
SELECT CUD.Definition, CONVERT(NVARCHAR,ContentText) ContentText, subdefinition from C INNER JOIN cu ON C.cuID = cu.cuID INNER JOIN CUD ON cu.cudID = CUD.cudID
WHERE cu.cudID = 1
) as p
PIVOT (MAX(ContentText) FOR subdefinition IN ([Title],[Teaser],[Story])
) as pvt

Open in new window

Chris Luttrell

I bet Story could be too long, thats why it is NTEXT to begin with. I bet reb73's query is going to be the only way to get it to work. Hate doing the multiple subqueries, but sometimes you have to do what it takes to get the output.
Good Luck. I've had to work on Article databases before, FUN!

pmodiano1

ASKER

Hi reb73,

That does not do it. C.Corder is not related to CUD.cudID. It is the order in which the stories should be listed. This means, in my example the results should look like this:

Title Teaser Story
Story 1 title Story 1 teaser Story 1 body (sorder=1)
Story 2 title Story 2 teaser Story 2 body (sorder=2)

Thanks,

Paul.

reb73

Ok, so you don't need the CUD.Definition in the results at all..

Try -

SELECT  C1.COrder
        ,(SELECT ContentText FROM C WHERE C.COrder = C1.COrder AND C.CUId = 1) AS [Title]
        ,(SELECT ContentText FROM C WHERE C.COrder = C1.COrder AND C.CUId = 2) AS [Teaser]
        ,(SELECT ContentText FROM C WHERE C.COrder = C1.COrder AND C.CUId = 3) AS [Story]
FROM C C1
ORDER BY C1.COrder

Open in new window

pmodiano1

ASKER

reb73,

Your last query almost works. I ran it on a database with lots of data but pointed it to C.cuID that I know to have 2 stories. It listed (correctly!) the first one but duplicated the entry 30 times and was followed by 12 duplications of the second story. I tried to add DISTINCT so that the query began SELECT DISTINCT... but I got back a message 'The ntext data type cannot be selected as DISTINCT because it is not comparable.'

I don't quite know why it generated duplicate entries. What do you think?

Thanks,

Paul

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pmodiano1

ASKER

Thank you very much. You sure know your SQL!