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