Hello everyone,

I need to figure out how to de-normalize several one-to-many
relationships into a single row of data.

For reference, here is a small sample of the Normalized Tables:

Tabe Artist
-----------------------------------
AristID int not null identity,
ArtistFirstName varchar(50) not null,
ArtistLastName varchar(50) not null,
OldID int not null
.....

Table Address (store 2 addresses per artist)
----------------------------------
AddressID int not null identity,
ArtistID int not null,
Address varchar(255) not null,
City varchar(100) not null,
State varchar(2) not null,
Zip varchar(10) not null
......

Table RecordingMaster (store 4 masters per artist)
----------------------------------
RMID int not null indentity,
ArtistID int not null,
RMName varchar(100) not null,
RMReleaseDate datetime not null
.....

I need the row of my final table to look like this:

Table LabelMaster
---------------------------
LMID int not null
FirstName varchar(50) not null,
LastName varchar(50) not null,
Address1 varchar(255) not null,
City1 varchar(100) not null,
State1 varchar(2) not null,
Zip1 varchar(10) not null,
Address2 varchar(255) not null,
City2 varchar(100) not null,
State2 varchar(2) not null,
Zip2 varchar(10) not null,
Master1 varchar(100) not null,
Master2 varchar(100) not null,
Master3 varchar(100) not null,
Master4 varchar(100) not null

Some Data:
ARTIST
1, 'Curtis', 'Jackson', 543355
2, 'Marshall', 'Mathers', 666674

ADDRESS
1, 1,'1010 my way','Los Angeles','CA', '90005'
2, 1,'1020 my way','Chino','CA', '97899'
3, 2, '9876 Heleo Drive', 'Beverly Hills', 'CA', '90006'
4, 2, '5600 Leon Street', 'Barstow', 'CA', '91876'

RECORDINGMASTER
1,1,'Gunit 1','1/1/2001'
2,1,'Gunit 2','1/1/2002'
3,1,'Gunit 3','1/1/2003'
4,1,'Gunit 4','1/1/2004'
5,2,'EM 1','1/1/2001'
6,2,'EM 2','1/1/2002'
7,2,'EM 3','1/1/2003'
8,2,'EM 4','1/1/2004'

DESIRED ROWS

ROW #1
543355,'Curtis','Jackson','1010 my way','Los Angeles','CA',
'90005','1020 my way','Chino','CA', '97899','Gunit 1','Gunit 2','Gunit
3','Gunit 4',

ROW #2
666674,'Marshall','Mathers','9876 Heleo Drive', 'Beverly Hills', 'CA',
'90006','5600 Leon Street', 'Barstow', 'CA', '91876','EM 1','EM 2','EM
3','EM 4'


Thanks in advance for your help,
-V

If you are talking about truly normalized tables, then to create a
non-normalized resultset all you have do is a join.

( Btw, some people use the term normalization loosely in the context of
pivoting, transposing or cross tabulating SQL tables, which I am not sure
what you are asking for. )

But when I do a join, I get several rows in my resultset.  The ultimate
goal is to have one row with the all the values I'm looking for.

So essentially you are not talking about normalization.

This is simply pivoting the data for snapshot/reporting purposes. In SQL
Server 2000, there are no built in operators which can do this and so it is
recommended that you use a client side programming language or report writer
to do this. If you must do this at the server, you will have to write some
kludgy code, some examples of which can be found at:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574
http://www.bizdatasolutions.com/tsql/rowconcatenate.asp

In SQL Server 2005, there are some built in operators like PIVOT, CROSS
APPLY etc. which you can use to simplify the effort.

<snip>

ViLo,

-- My apologies.  While I did work with it for a while, and came
close, I did not solve the problem (note: and yet, see much furter
below for a last minute thought).  As far as I can tell, a far more
complicated solution involving dynamic SQL construction to specify
output sequential column names would be required (yes, I skipped
trying it).
-- I include the following in case it might be helpful.


-- Tables:


CREATE TABLE Artist
(ArtistID            IDENTITY      NOT NULL
,ArtistFirstName     VARCHAR(50)   NOT NULL
,ArtistLastName      VARCHAR(50)   NOT NULL
,OldID               INTEGER       NOT NULL
,CONSTRAINT pk_Artist
            PRIMARY KEY (ArtistID)
)

CREATE TABLE Address
(AddressID           IDENTITY      NOT NULL
,ArtistID            INTEGER       NOT NULL
,Address             VARCHAR(255)  NOT NULL
,City                VARCHAR(100)  NOT NULL
,State               VARCHAR(2)    NOT NULL
,Zip                 VARCHAR(10)   NOT NULL
,CONSTRAINT pk_Address
            PRIMARY KEY (AddressID)
,CONSTRAINT fk_Address_Artist
            FOREIGN KEY (ArtistID)
            REFERENCES Artist (ArtistID)
)

CREATE TABLE RecordingMaster
(RMID                IDENTITY      NOT NULL
,ArtistID            INTEGER       NOT NULL
,RMName              VARCHAR(100)  NOT NULL
,RMReleaseName       DATETIME      NOT NULL
,CONSTRAINT pk_RecordingMaster PRIMARY KEY (RMID)
,CONSTRAINT fk_RecordingMaster_Artist
            FOREIGN KEY (ArtistID)
            REFERENCES Artist (ArtistID)
)


-- Sample Data:

INSERT Artist
(ArtistFirstName
,ArtistLastName
,OldID
)
SELECT 'Curtis', 'Jackson', 543355 UNION
SELECT 'Marshall', 'Mathers', 666674

INSERT Address
(ArtistID
,Address
,City
,State
,Zip
)
SELECT 1, '1010 my way', 'Los Angeles', 'CA', '90005' UNION
SELECT 1, '1020 my way', 'Chino','CA', '97899' UNION
SELECT 2, '9876 Heleo Drive', 'Beverly Hills', 'CA', '90006' UNION
SELECT 2, '5600 Leon Street', 'Barstow', 'CA', '91876'

INSERT RecordingMaster
(ArtistID
,RMName
,RMReleaseName
)
SELECT 1, 'Gunit 1', '1/1/2001' UNION
SELECT 1, 'Gunit 2', '1/1/2002' UNION
SELECT 1, 'Gunit 3', '1/1/2003' UNION
SELECT 1, 'Gunit 4', '1/1/2004' UNION
SELECT 2, 'EM 1', '1/1/2001' UNION
SELECT 2, 'EM 2', '1/1/2002' UNION
SELECT 2, 'EM 3', '1/1/2003' UNION
SELECT 2, 'EM 4', '1/1/2004'
go


SELECT * FROM Artist
SELECT * FROM Address
SELECT * FROM RecordingMaster


-- There will be two functions:

--  Note: The code inside the function is based on a row
concatenation scheme devised by someone else on this newsgroup.


-- Address Concat
CREATE FUNCTION dbo.AddressConcat
(@ArtistID        INTEGER
)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @Addresses VARCHAR(300)
         ,@delimiter CHAR(2)
      SET @ArtistID = 1
      SET @delimiter = ', '
   SELECT @Addresses =
          COALESCE(@Addresses + @delimiter, '')
        + (SELECT (AD01.Address
                +  ' ' + AD01.City
                +  ' ' + AD01.State
                +  ' ' + AD01.Zip)
             FROM Address as AD01
            WHERE AD01.AddressID     = AD1.AddressID)
     FROM Address AS AD1
    WHERE AD1.ArtistID = @ArtistID
  RETURN(@Addresses)
END;
go

-- RName Concat
CREATE FUNCTION dbo.RMNameConcat
(@ArtistID        INTEGER
)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RMNames VARCHAR(300)
         ,@delimiter CHAR(2)
      SET @ArtistID = 1
      SET @delimiter = ', '
   SELECT @RMNames =
          COALESCE(@RMnames + @delimiter, '')
        + (SELECT RM01.RMName
             FROM RecordingMaster as RM01
            WHERE RM01.RMID     = RM1.RMID)
     FROM Recordingmaster AS RM1
    WHERE RM1.ArtistID = @ArtistID
  RETURN(@RMNames)
END;
go


-- Query:


SELECT AR1.OldID
      ,AR1.ArtistFirstName
      ,AR1.ArtistLastName
      ,dbo.AddressConcat(AR1.ArtistID) AS Addresses
      ,dbo.RMNameConcat(AR1.ArtistID) As RecordingMasters
  FROM Artist AS AR1


/*
DROP TABLE Artist
DROP TABLE Address
DROP TABLE Recording Master

DROP FUNCTION dbo.AddressConcat
DROP FUNCTION dbo.RMNameConcat
*/


-- Last minute thought:

-- (This is totally fixed like it's in concrete, and is aboslutely
--  dependent on an ascending sequential integer sequence for a
--  primary key, but given the sample data and the specified
--  output table, that may not be a problem).

SELECT AR1.ArtistID
      ,AR1.ArtistFirstName
      ,AR1.ArtistLastName
      ,(SELECT AD01.Address
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = ((AR1.ArtistID * 2) - 1)) AS
Address1
      ,(SELECT AD01.City
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = ((AR1.ArtistID * 2) - 1)) AS City1
      ,(SELECT AD01.State
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = ((AR1.ArtistID * 2) - 1)) AS State1
      ,(SELECT AD01.Zip
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = ((AR1.ArtistID * 2) - 1)) AS Zip1
      ,(SELECT AD01.Address
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = (AR1.ArtistID * 2)) AS Address2
      ,(SELECT AD01.City
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = (AR1.ArtistID * 2)) AS City2
      ,(SELECT AD01.State
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = (AR1.ArtistID * 2)) AS State2
      ,(SELECT AD01.Zip
          FROM Address AS AD01
         WHERE AD01.ArtistID = AR1.ArtistID
           AND AD01.AddressID = (AR1.ArtistID * 2)) AS Zip2
      ,(SELECT RM01.RMName AS RM01
          FROM RecordingMaster AS RM01
         WHERE RM01.ArtistID = AR1.ArtistID
           AND RM01.RMID = ((AR1.ArtistID * 4) - 3)) AS Master1
      ,(SELECT RM01.RMName AS RM01
          FROM RecordingMaster AS RM01
         WHERE RM01.ArtistID = AR1.ArtistID
           AND RM01.RMID = ((AR1.ArtistID * 4) - 2)) AS Master2
      ,(SELECT RM01.RMName AS RM01
          FROM RecordingMaster AS RM01
         WHERE RM01.ArtistID = AR1.ArtistID
           AND RM01.RMID = ((AR1.ArtistID * 4) - 1)) AS Master3
      ,(SELECT RM01.RMName AS RM01
          FROM RecordingMaster AS RM01
         WHERE RM01.ArtistID = AR1.ArtistID
           AND RM01.RMID = (AR1.ArtistID * 4)) AS Master4
  FROM Artist AS AR1


-- That actually appears like it may work,
-- but will require a lot more testing.


Sincerely,

Chris O.

Thanks Chris.  I will go ahead and give that a try.

On 11 Nov 2005 00:41:43 -0800, ViLo wrote:

Hi ViLo,

This is not as easy as it sounds. But if I may assume that there will
never be more than 2 addresses and never more than 4 recordingmasters
per artist, then you might wish to try something like this:

SELECT          a.ArtistFirstName, a.ArtistLastName,
                a1.Address, a1.City, a1.State, a1,Zip,
                a2.Address, a2.City, a2.State, a2,Zip,
                r1.RMName, r2.RMName, r3.RMName, r4.RMName
FROM            Artist          AS  a
LEFT OUTER JOIN Address         AS  a1
           ON   a1.ArtistID      =  a.ArtistID
           AND  a1.AddressID     = (SELECT MIN(AddressID)
                                    FROM   Address
                                    WHERE  ArtistID  = a.ArtistID)
LEFT OUTER JOIN Address         AS  a2
           ON   a2.ArtistID      =  a.ArtistID
           AND  a2.AddressID     = (SELECT MIN(AddressID)
                                    FROM   Address
                                    WHERE  ArtistID  = a.ArtistID
                                    AND    AddressID > a1.AddressID)
LEFT OUTER JOIN RecordingMaster AS  r1
           ON   r1.ArtistID      =  a.ArtistID
           AND  r1.RMID          = (SELECT MIN(RMID)
                                    FROM   RecordingMaster
                                    WHERE  ArtistID  = a.ArtistID)
LEFT OUTER JOIN RecordingMaster AS  r2
           ON   r2.ArtistID      =  a.ArtistID
           AND  r2.RMID          = (SELECT MIN(RMID)
                                    FROM   RecordingMaster
                                    WHERE  ArtistID  = a.ArtistID
                                    AND    RMID      > r1.RMID)
LEFT OUTER JOIN RecordingMaster AS  r3
           ON   r3.ArtistID      =  a.ArtistID
           AND  r3.RMID          = (SELECT MIN(RMID)
                                    FROM   RecordingMaster
                                    WHERE  ArtistID  = a.ArtistID
                                    AND    RMID      > r2.RMID)
LEFT OUTER JOIN RecordingMaster AS  r4
           ON   r4.ArtistID      =  a.ArtistID
           AND  r4.RMID          = (SELECT MIN(RMID)
                                    FROM   RecordingMaster
                                    WHERE  ArtistID  = a.ArtistID
                                    AND    RMID      > r3.RMID)

(untested - see www.aspfaq.com/5006 if you prefer a rested reply)

Best, Hugo

Cursor working in this stored Procedure??
Best solution, iterate over millions records and call extended sp
convert INT
A difficult Combining Rows problem
Processes in Sysprocesses