Flatten parent relationships with children in SQL Server

This "smoothing" operation is referred to as a multi-column dynamic bar. It is dynamic because the pivot column values ​​are not known at design time, and it is multicolored because you have the pivot column values ​​"age" and "name".

To make a multi-column dynamic pivot in SQL you need to use dynamic sql and resort to case statements. SQL Pivot statement does not work with multi-column pivot. This is collapsed for implementation.

I don't think SQL is the best language for doing multi-column dynamic bar. I think this is better done on the client side.

The following C # method returns a data table containing the query you are looking for:

public DataTable GetPivotedPeople()
{
    using (var ds = new MyDataService())
    {
        return ds.PersonRepository
            .Query("Household")
            .OrderBy(PersonFields.HouseHoldId, PersonFields.Address, PersonFields.City, PersonFields.State, PersonFields.Zip)
            .Pivot(
                new PivotTransform
                {
                    PivotColumnName = PersonFields.PersonId,
                    ValueColumnName = PersonFields.Name,
                    GetPivotedColumnName = (personId) => "Person" + personId.ToString() + "Name"
                },
                new PivotTransform
                {
                    PivotColumnName = PersonFields.PersonId,
                    ValueColumnName = PersonFields.Age,
                    GetPivotedColumnName = (personId) => "Person" + personId.ToString() + "Age"
                }
            );
    }
}

      

        
        
        
      

    

This is the contents of the returned data table:

+-------------+----------+---------+-------+-------+-------------+-------------+------------+------------+
| HouseHoldId | Address  |   Ciy   | State |  Zip  | Person1Name | Person2Name | Person1Age | Person2Age |
+-------------+----------+---------+-------+-------+-------------+-------------+------------+------------+
|           1 | 123 Main | Anytown | CA    | 90121 | John        | Jane        |         32 |         29 |
+-------------+----------+---------+-------+-------+-------------+-------------+------------+------------+

      

        
        
        
      

    

It uses EntityLite , a micro ORM that I developed. It queries the Person_HouseHold view and rotates the rows on the client side. Here you have your sql script:

CREATE DATABASE DynamicPivot
GO
USE DynamicPivot
GO
CREATE TABLE Households
(
    HouseholdId int IDENTITY(1,1) PRIMARY KEY,
    [Address] nvarchar(128) NOT NULL,
    City nvarchar(128) NOT NULL,
    [State] nvarchar(128) NOT NULL,
    Zip nvarchar(128) NOT NULL
);
INSERT INTO Households ([Address], City, [State], Zip) 
VALUES (N'123 Main', N'Anytown', N'CA', N'90121');
GO

CREATE TABLE People
(
    PersonId int IDENTITY(1,1) PRIMARY KEY,
    HouseHoldId int NOT NULL CONSTRAINT FK_People_Households REFERENCES HouseHolds(HouseholdId),
    Name nvarchar(128) NOT NULL,
    Age int NOT NULL

);
INSERT INTO People(HouseHoldId, Name, Age) VALUES
(1, N'John', 32), (1, 'Jane', 29)

GO

CREATE VIEW Person_Household
AS
    SELECT 
        P.PersonId, P.HouseHoldId, P.Name, P.Age,
        H.[Address], H.City, H.[State], H.Zip
    FROM
        dbo.People P INNER JOIN dbo.Households H
            ON P.HouseHoldId = H.HouseholdId

      

        
        
        
      

    

I wrote this article on CodeProject. It explains how to do pivot using sql and how to do client side pivot using EntityLite or using raw ADO.NET. So, to do a client side pivot, you don't need to use EntityLite.