Denormalized table

Question

Post reply

Denormalized table

colaforever

Say Hey Kid

Points: 699
More actions
August 24, 2008 at 12:17 pm

#211497

I have a denormalised table with each record representing a projectID. In our projects we have stakeholders who contribute to the project, now the table i have to work with is built with a column for each stakeholder (8 in total), basically bad design, however, I want to build a report where i have in each row the project details and in that same row the stakeholders of the project, without repeating the project details each time a new stakeholder is found. I started off with the following query:-
select projectID, max(fieldname2), sum(fieldname3), max(stakeholderA) from tablename
where ......
group by fieldname
union
select projectID, max(fieldname2), sum(fieldname3), max(stakeholder) from tablename
where ......
group by fieldname
however and correctly so the projectID record is repeated in a new row when there is more than one stakeholder for a given projectID as follows:
projectA, xxxx, yyyy, stakeholderA
projectA, xxxx, yyyy, stakeholderB
....
I understand that this is the correct behaviour however, I don't want this behaviour, rather I would like something like
projectA, xxxx, yyyy, stakeholderA
stakeholderB
I think this is not possible directly in SQL, however, am not sure, any advice would be much appreciated.
CHEERS!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Think you should read Jeff Moden's latest article

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

By Jeff Moden, 2008/08/19

at http://www.sqlservercentral.com/articles/T-SQL/63681/

It is a great article on what it appears you want to perform

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

Heh... thanks for the plug, BitBucket!

A way kind of backwards from Cross Tabs is shown in the following article...

http://www.sqlservercentral.com/articles/Test+Data/61572/

The method would allow you to concatenate all the stakeholders into a single column.

If you want a tested answer, if you could provide a CREATE TABLE statement and some data according to the links in both BitBucket's and my signature line, I believe we could probably churn something out for you... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

colaforever Say Hey Kid Points: 699 More actions · Answer 3

Thanks guys but this is not what i was looking for. I know how cross tabs and pivots work.

I actually managed to solve the problem using SQL Server Reporting Services

The way to do it is using Reporting Services groups and layer groups over each other.

Thanks anyways

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Agh! My bad... I didn't read the original post as well as I should have. Throwing in a partitioned row number along with the a method similar to the cross tab method works just fine... without reporting services if you don't need it. Sorry for the confusion...

--===== Create and populate a test table.

-- This is NOT a part of the solution

DECLARE @DemoTable TABLE

(ProjectName VARCHAR(10), Field1 VARCHAR(10), Field2 VARCHAR(10), StakeHolder VARCHAR(15))

INSERT INTO @DemoTable

(ProjectName, Field1, Field2, StakeHolder)

SELECT 'projectA','xxxx','yyyy','stakeholderA' UNION ALL

SELECT 'projectA','xxxx','yyyy','stakeholderB' UNION ALL

SELECT 'projectA','xxxx','yyyy','stakeholderC' UNION ALL

SELECT 'projectB','xxyy','xxyy','stakeholderB' UNION ALL

SELECT 'projectC','aaaa','bbbb','stakeholderB' UNION ALL

SELECT 'projectC','aaaa','bbbb','stakeholderC' UNION ALL

SELECT 'projectD','cc','dd','stakeholderA' UNION ALL

SELECT 'projectD','cc','dd','stakeholderB' UNION ALL

SELECT 'projectD','cc','dd','stakeholderD' UNION ALL

SELECT 'projectD','cc','dd','stakeholderJ' UNION ALL

SELECT 'projectD','cc','dd','stakeholderK' UNION ALL

SELECT 'projectD','cc','dd','stakeholderL' UNION ALL

SELECT 'projectD','cc','dd','stakeholderM' UNION ALL

SELECT 'projectD','cc','dd','stakeholderN' UNION ALL

SELECT 'projectD','cc','dd','stakeholderO' UNION ALL

SELECT 'projectD','cc','dd','stakeholderP' UNION ALL

SELECT 'projectD','cc','dd','stakeholderC' UNION ALL

SELECT 'projectD','cc','dd','stakeholderZ'

;WITH

cteCreateRank AS

(--==== Cte returns data with ordinal ranked rows in sorted order

SELECT ROW_NUMBER() OVER (PARTITION BY ProjectName, Field1, Field2

ORDER BY ProjectName, Field1, Field2, StakeHolder) AS RowNum,

ProJectName, Field1, Field2, StakeHolder

FROM @DemoTable

)--==== This decides when to print the first 3 columns of data

SELECT CASE WHEN RowNum = 1 THEN ProjectName ELSE '' END AS ProjectName,

CASE WHEN RowNum = 1 THEN Field1 ELSE '' END AS Field1,

CASE WHEN RowNum = 1 THEN Field2 ELSE '' END AS Field2,

StakeHolder

FROM cteCreateRank

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)