Pivot/Unpivot master detail?
Hello,
SQL Server 2008.
I have a master/detail relationship between two tables. I need to write an export that returns the columns from the master table followed by the entries in the details table.
I guess i need to pivot/unpivot the details table? The details table has two columns. These are code and value. However i dont know beforehand what the possible contents of code can be.
The output should look:
id,Name,Startdate,Code,Val
Any ideas?
SQL Server 2008.
I have a master/detail relationship between two tables. I need to write an export that returns the columns from the master table followed by the entries in the details table.
I guess i need to pivot/unpivot the details table? The details table has two columns. These are code and value. However i dont know beforehand what the possible contents of code can be.
The output should look:
id,Name,Startdate,Code,Val
CREATE TABLE [dbo].[Master](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Startdate] [date] NULL)
CREATE TABLE [dbo].[Details](
[id] [int] NOT NULL,
[MasterId] [int] NULL,
[Code] [nvarchar](20) NULL,
[Value] [nvarchar](50) NULL)Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kevin Cross
An alternative would be to rank the code-value pairs based on the foreign key to the master table. Then derived tables, select codes and values independently with the rank as suffix. You can join the pivots to get code, value, code, value, ... this approach works with conditional aggregates versus pivots also if there is a fixed maximum number of codes/values per master record.