• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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.

0
pmodiano1
Asked:
pmodiano1
  • 4
  • 4
  • 3
1 Solution
 
reb73Commented:
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

0
 
Chris LuttrellSenior Database ArchitectCommented:
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

0
 
pmodiano1Author Commented:
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?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
reb73Commented:
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

0
 
Chris LuttrellSenior Database ArchitectCommented:
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

0
 
Chris LuttrellSenior Database ArchitectCommented:
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!
0
 
pmodiano1Author Commented:
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.
0
 
reb73Commented:
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

0
 
pmodiano1Author Commented:
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
0
 
reb73Commented:
That's because we are using the C for the main select and it will have duplicate COrder values..

 A small change -


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 (select distinct COrder from C) C1
ORDER BY C1.COrder

Open in new window

0
 
pmodiano1Author Commented:
Thank you very much.  You sure know your SQL!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now