RE: [SQLDownUnder] Transpose Dynamic Table or Denormalise a Table

[Williams, Thomas]

Title: Message

Hi Scott,

 

A row like:

    Parent Child1 Child2 Child3 etc.

with variable number of columns might be hard to consume from a client.

 

What about a row that returns:

    Parent NumberOfChildren DelimitedListOfChildren

where "DelimitedListOfChildren" is a string that can be pulled apart later (and "NumberOfChildren" could be used to help)?

 

You could then call a function to get that string (like June suggested earlier) to go along with your sample. Rough SQL:

> --function to put together delimited string of names for a passed parent id
> CREATE FUNCTION dbo.fnGetNameString(@ParentId INT) RETURNS VARCHAR(7998)
> AS
> BEGIN 
>     DECLARE @ReturnString VARCHAR(7998) 
>     
>     SELECT  @ReturnString = COALESCE(@ReturnString + '|', '') + C.[Name]
>     FROM    dbo.[children] C 
>     WHERE   C.ParentId = @ParentId 
>     
>     RETURN (@ReturnString)
> END 
> GO
>
> --select statement
>
> SELECT  C.ParentId, COUNT(*) AS NumberOfChildren, dbo.fnGetNameString(C.ParentId) AS DelimitedListOfChildren
> FROM    dbo.[children] C
> GROUP BY C.ParentId
>
> --output
>
> ParentId NumberOfChildren DelimitedListOfChildren
> 1 3 Scott|Bob|Smith
> 2 3 Carly|Don|Angela
> 3 4 Sue|Judy|Jane|Jill

Good luck,

 

Thomas Williams

http://dotnetjunkies.com/WebLog/thomasswilliams/

 

 

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of zzsober
Sent: Thursday, 8 February 2007 12:59
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] Transpose Dynamic Table or Denormalise a Table

Thanks Greg, Jarrad & William.

 

Re Greg's email - I did stumble across this product yesterday via google, but I discounted it and also the whole crosstab idea because I'm not performing a summation on the data, merely transforming data from being in many rows to being repeated on a single row in many columns.

 

The ultimate goal being to have a single row for each parent that shows all their children in separate columns.

 

I feel like I'm the only one to ever have to do this - perhaps I should take from that that I'm approaching the solution from the wrong perspective.

 

Regards,

Scott

 

 

On 2/8/07, Greg Linwood <greg.linwood@xxxxxxxxxxxxxxxxxx> wrote:

Another great solution to the crosstab problem is GeckoWare's crosstab builder.
http://www.geckoware.com.au/

Crosstab-builder isn't free, but it's also not expensive. It is seriously fast though, as it's an in proc xp_ (which works equally well on SQL 2000 and SQL 2005). What's more it's locally supported by a well established Australian tools vendor who has been in the business for many years (Gecko also make SQLScribe). Some might have seen the SQL Server User Group session in Melbourne delivered by GeckoWare's Greg Obleshchuk who gave an expert comparison of SQL 05's CLR programming vs SQL xp_ C++ ODS programming in Nov 2005. In that session he discussed why an xp_ will often out-perform TSQL cross-tab / transform solutions. There's too much info to reproduce here but keep in mind that even TSQL solutions have to materialise & sort this type of command in tempdb, whereas a library solution can do it all in memory, which will be very fast unless you're dealing with massive results which might need advanced page management.

Cheers,

Greg Linwood
Australian Director & Mentor
Solid Quality Learning
www.SolidQualityLearning.com.au < https://me-au.server-secure.com/exchweb/bin/redir.asp?URL="" >
greg@xxxxxxxxxxxxxxxxxxxxxxxx <mailto:greg@xxxxxxxxxxxxxxxxxxxxxxxx>
http://blogs.sqlserver.org.au/blogs/greg_linwood/default.aspx < https://me-au.server-secure.com/exchweb/bin/redir.asp?URL="" >

"Solid Quality Learning is the trusted, global provider of advanced
education and solutions for the entire Microsoft database platform"

________________________________

From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx on behalf of Williams, Thomas
Sent: Thu 8/02/2007 10:54 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] Transpose Dynamic Table or Denormalise a Table


Hey Scott, have you tried "sp_Crosstab" from http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html?track=NL-464&ad=525294USCA ?

I have used it with success before. Not the most optimum solution, but sure beats hand-coding like in my blog post at http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx.

Cheers,

Thomas Williams
http://dotnetjunkies.com/WebLog/thomasswilliams/




       -----Original Message-----
       From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of zzsober
       Sent: Thursday, 8 February 2007 11:31
       To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
       Subject: Re: [SQLDownUnder] Transpose Dynamic Table or Denormalise a Table


       Hi Aku,

       No, its a SQL 2000 database...

       I'm starting to think you're right about doing it outside of the db.... I was trying to keep all the data logic in the database, but I just can't get it to work!! But if anyone has tips, I'm still listening!!

       Regards,

       Scott



       On 2/8/07, Aku Sopanen <Aku.Sopanen@xxxxxxxxxx> wrote:

               Do you have 2005?  If so, the new T-SQL addition of "PIVOT"  may help. Otherwise you are likely to achieve this easier outside the db.




________________________________


               From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx <mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx> ] On Behalf Of zzsober
               Sent: Thursday, 8 February 2007 10:21 AM
               To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
               Subject: [SQLDownUnder] Transpose Dynamic Table or Denormalise a Table



               Hi,



               Can someone help point me in a direction or offer a solution to a problem that is ifffuriating me at the moment?



               Effectively I need to transpose a table's rows into columns or denormalise it.



               For example I've got a "Children" table, and I need the children's names to appear in separate columns in a temporary table.  Before you ask, the reason I am doing this is that the temporary table will be then used as a datasource for a Microsoft Word mail merge which doesn't support relational mail merging...



               My Example structure of the Children's table is:-



               CREATE TABLE [children] (
                [ChildId] [int] NULL ,
                [ParentId] [int] NULL ,
                [Name] [nvarchar] (10),
                [FavouriteColour] [nvarchar] (10)
               )
               GO



               Example data in Children's table is:-



               -- populate children details with some dummy data
               INSERT INTO [children] values (1, 1, 'Scott', 'Blue')
               INSERT INTO [children] values (2, 1, 'Bob', 'Red')
               INSERT INTO [children] values (3, 1, 'Smith', 'Blue')
               INSERT INTO [children] values (4, 2, 'Carly', 'Red')
               INSERT INTO [children] values (5, 2, 'Don', 'Green')
               INSERT INTO [children] values (6, 2, 'Angela', 'Blue')
               INSERT INTO [children] values (7, 3, 'Sue', 'Red')
               INSERT INTO [children] values (8, 3, 'Judy', 'Green')
               INSERT INTO [children] values (9, 3, 'Jane', 'Blue')
               INSERT INTO [children] values (10, 3, 'Jill', 'Red')



               Then in a temporary table I want to be able to produce a record source that looks something like this for a particular parent.  At moment, only one parent would be mail merged at a time.



               ParentId    name1        favouritecolour1         name2        favouritecolour2        name3... etc
               ----------- ---------------------------------------------------------------------------------------
               3           Sue          Red                      Judy         Green                   Jane



               I've tried (and failed so far) to do this dynamically with cursors and the such.



               Anyone tried to do something like this before??



               Regards,



               Scott




























               to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com <http://mailenable.com/>  - List managed by http://www.readify.net <http://www.readify.net/>

               to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com < http://mailenable.com/>  - List managed by http://www.readify.net <http://www.readify.net/>


       to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net