Like a pivot or crosstab but maybe not...

Last Post 14 Apr 2008 02:05 PM by SwePeso. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
nathankatcgfdotorg
New Member
New Member

--
01 Apr 2008 05:34 PM
I have the following data (see below for create and insert statements):

set name owner
1/kim/1
1/fluffy/0
2/mark/1
2/fido/0
3/tina/1
3/tracey/1
3/bob/0
5/kate/1
5/trixi/0
5/speed/0

And I want to break it out like this:

set/owner1/owner2/owner3/pet1/pet2/pet3
1/kim///fluffy//
2/mark///fido//
3/tina/tracey//bob//
5/kate///trixi/speed/

I used slashes only to show in this example because tabs wasn't working out. Any ideas?


Data is:

CREATE TABLE [dbo].[tblTable] (
[set] [nvarchar] (50) NULL ,
[name] [nvarchar] (50) NULL ,
[owner] [bit] NULL
) ON [PRIMARY]

INSERT INTO tblTable ([set], name, owner) VALUES (1, 'kim',1);
INSERT INTO tblTable ([set], name, owner) VALUES (1, 'fluffy',0);
INSERT INTO tblTable ([set], name, owner) VALUES (2, 'mark',1);
INSERT INTO tblTable ([set], name, owner) VALUES (2, 'fido',0);
INSERT INTO tblTable ([set], name, owner) VALUES (3, 'tina',1);
INSERT INTO tblTable ([set], name, owner) VALUES (3, 'tracey',1);
INSERT INTO tblTable ([set], name, owner) VALUES (3, 'bob',0);
INSERT INTO tblTable ([set], name, owner) VALUES (5, 'kate',1);
INSERT INTO tblTable ([set], name, owner) VALUES (5, 'trixi',0);
INSERT INTO tblTable ([set], name, owner) VALUES (5, 'speed',0);

SQLUSA
New Member
New Member

--
02 Apr 2008 01:14 AM
You have to build a table with the set of attributes in the desired format - set/owner1/owner2/owner3/pet1/pet2/pet3 .

Populate the table.

Export the table as csv (or similar format).

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/
nathankatcgfdotorg
New Member
New Member

--
02 Apr 2008 08:26 AM
You can distinguish between pet and owner by the bitflag in the owner field. 1 = owner, 0 = pet.

Kalman, I should have specified that I'm looking for a solution that can work on the fly, and specifically the problem is *how* to get it into the desired format (rearrangment).

THANKS!!

P.S. I thought about getting two views, one of owners and one of pets and joining them on the set field...but it didn't get it into the final result desired and I couldn't see how to get it from there to the final result desired any better than what I had in the beginning.
SwePeso
New Member
New Member

--
07 Apr 2008 03:05 AM
What eludes me is the knowledge of which owner belongs to which pet?
Because you can' t rely on the physical order of records in the database.
SwePeso
New Member
New Member

--
07 Apr 2008 03:15 AM
See final suggestion further down.
nathankatcgfdotorg
New Member
New Member

--
07 Apr 2008 09:46 AM
Thanks. I was hoping to get out of a temp table and therefore any data modification (updates or inserts) but didn't know if that was possbile.

Thanks for your solution.
SQLUSA
New Member
New Member

--
07 Apr 2008 11:39 AM
I still see too much hard-wiring.

Just create a table in the desired format.

Insert whatever columns can be inserted with a simple query (initial population).

Update the rest of the columns with simple queries (completing population).

Do use lookup tables to avoid hard-wiring.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SwePeso
New Member
New Member

--
14 Apr 2008 02:05 PM
Until Kalman decides to join in again,
try this code

SELECT y.[Set],
y.Owner1,
CASE
WHEN y.Owner1 = y.Owner2 THEN s.[Name]
ELSE y.Owner2
END AS Owner2,
CASE
WHEN y.Owner1 = y.Owner2 THEN NULL
ELSE s.[Name]
END AS Owner3,
y.Pet1,
CASE
WHEN y.Pet1 = y.Pet2 THEN s.[Name]
ELSE y.Pet2
END AS Pet2,
CASE
WHEN y.Pet1 = y.Pet2 THEN NULL
ELSE s.[Name]
END AS Pet3
FROM (
SELECT [Set],
MIN(CASE WHEN [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner1,
MAX(CASE WHEN [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner2,
MIN(CASE WHEN [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet1,
MAX(CASE WHEN [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet2
FROM tblTable
GROUP BY [Set]
) AS y
LEFT JOIN tblTable AS s ON s.[Set] = y.[Set]
AND (
s.[Name] NOT IN (y.Owner1, y.Owner2)
AND s.[Owner] = 1
OR
s.[Name] NOT IN (y.Pet1, y.Pet2)
AND s.[Owner] = 0
)
SwePeso
New Member
New Member

--
14 Apr 2008 02:31 PM
And the SQL Server 2005 solution

SELECT [Set],
MAX(CASE WHEN ColID = 1 AND [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner1,
MAX(CASE WHEN ColID = 2 AND [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner2,
MAX(CASE WHEN ColID = 3 AND [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner3,
MAX(CASE WHEN ColID = 1 AND [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet1,
MAX(CASE WHEN ColID = 2 AND [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet2,
MAX(CASE WHEN ColID = 3 AND [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet3
FROM (
SELECT [Set],
[Name],
[Owner],
ROW_NUMBER() OVER (PARTITION BY [Set], [Owner] ORDER BY [Name]) AS ColID
FROM @tblTable
) AS p
GROUP BY [Set]
You are not authorized to post a reply.

Acceptable Use Policy