• elham_azizi_62 (10/5/2015)


    dear J Livingston.

    I mean dynamic generating columns.in fact if my column is null,column isn't shown.

    ....not entirely sure that I understand your requirements....but try this

    CREATE TABLE #sampledata(

    PersonID int NOT NULL,

    FirstName varchar(50) NULL,

    Surname varchar(50) NULL,

    SalaryType varchar(50) NULL,

    SalaryAmount int NULL

    )

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1188, N'Ali', N'Ahmadi', N'BaseSalary', 1213232323)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1245, N'ahmad', N'Alipour', N'ExtraSalary', 2526961)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1188, N'ali', N'ahmadi', N'ExtraSalary', 54585)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1478, N'sara', N'Emami', N'BaseSalary', 548745)

    INSERT #sampledata (PersonID, FirstName, Surname, SalaryType, SalaryAmount) VALUES (1188, N'Ali', N'Ahmadi', N'SpecialSalary', 245832)

    SELECT * FROM #sampledata;

    with cte as (

    SELECT

    PersonID

    , FirstName

    , Surname

    , SalaryType

    , SalaryAmount

    , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY SalaryType) AS rn

    FROM #sampledata

    )

    SELECT

    PersonID

    , FirstName

    , Surname

    , ISNULL(MAX(CASE WHEN rn = 1 THEN SalaryType END ) , '') as SalaryType

    , ISNULL(MAX(CASE WHEN rn = 1 THEN CAST(SalaryAmount as varchar(10)) END) , '') as SalaryAmount

    , ISNULL(MAX(CASE WHEN rn = 2 THEN SalaryType END ) , '') as SalaryType

    , ISNULL(MAX(CASE WHEN rn = 2 THEN CAST(SalaryAmount as varchar(10)) END) , '') as SalaryAmount

    , ISNULL(MAX(CASE WHEN rn = 3 THEN SalaryType END ) , '') as SalaryType

    , ISNULL(MAX(CASE WHEN rn = 3 THEN CAST(SalaryAmount as varchar(10)) END) , '') as SalaryAmount

    FROM cte

    GROUP BY PersonID, FirstName, Surname

    DROP TABLE #sampledata

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day