October 30, 2017 at 12:12 am
Hello Experts,
I have a table with the following data:
| FieldName | FieldValue | RecordStatus |
| Coverage | NULL | 1 |
| Premium | NULL | 2 |
| F1 | 100 | 1 |
| F2 | 100 | 1 |
| Coverage | 200 | 1 |
| Premium | 10 | 1 |
| F1 | 50 | 1 |
| F2 | NULL | 3 |
| Coverage | 300 | 1 |
| Premium | 45 | 1 |
| F1 | 24 | 1 |
| F2 | NULL | 1 |
| Coverage | 450 | 3 |
| Premium | 12 | 3 |
| F1 | 50 | 1 |
| F2 | NULL | 1 |
I would like to generate output like the following:
| Coverage | Coverage_RecordStatus | Premium | Premium_RecordStatus | F1 | F1_RecordStatus | F2 | F2_RecordStatus |
| NULL | 1 | NULL | 2 | 100 | 1 | 100 | 1 |
| 200 | 1 | 10 | 1 | 50 | 1 | NULL | 3 |
| 300 | 1 | 45 | 1 | 24 | 1 | NULL | 1 |
| 450 | 3 | 12 | 3 | 50 | 1 | NULL | 1 |
Kindly note, here, FieldName are not four as in the table, it might change dynamically.
Find below the data:
CREATE TABLE #Results
(
FieldName nvarchar(50),
FieldValue nvarchar(50),
RecordStaus int
);
INSERT INTO #Results(FieldName,FieldValue,RecordStaus)
VALUES ('Coverage',NULL,1)
,('Premium',NULL,2)
,('F1',100,1)
,('F2',100,1)
,('Coverage',200,1)
,('Premium',10,1)
,('F1',50,1)
,('F2',NULL,3)
,('Coverage',300,1)
,('Premium',45,1)
,('F1',24,1)
,('F2',NULL,1)
,('Coverage',450,3)
,('Premium',12,3)
,('F1',50,1)
,('F2',NULL,1);
Thanks,
Naveen J V
October 30, 2017 at 1:05 am
Naveen J V - Monday, October 30, 2017 12:12 AMHello Experts,
I have a table with the following data:
FieldName FieldValue RecordStatus Coverage NULL 1 Premium NULL 2 F1 100 1 F2 100 1 Coverage 200 1 Premium 10 1 F1 50 1 F2 NULL 3 Coverage 300 1 Premium 45 1 F1 24 1 F2 NULL 1 Coverage 450 3 Premium 12 3 F1 50 1 F2 NULL 1 I would like to generate output like the following:
Coverage Coverage_RecordStatus Premium Premium_RecordStatus F1 F1_RecordStatus F2 F2_RecordStatus NULL 1 NULL 2 100 1 100 1 200 1 10 1 50 1 NULL 3 300 1 45 1 24 1 NULL 1 450 3 12 3 50 1 NULL 1 Kindly note, here, FieldName are not four as in the table, it might change dynamically.
Find below the data:
CREATE TABLE #Results
(
FieldName nvarchar(50),
FieldValue nvarchar(50),
RecordStaus int
);
INSERT INTO #Results(FieldName,FieldValue,RecordStaus)
VALUES ('Coverage',NULL,1)
,('Premium',NULL,2)
,('F1',100,1)
,('F2',100,1)
,('Coverage',200,1)
,('Premium',10,1)
,('F1',50,1)
,('F2',NULL,3)
,('Coverage',300,1)
,('Premium',45,1)
,('F1',24,1)
,('F2',NULL,1)
,('Coverage',450,3)
,('Premium',12,3)
,('F1',50,1)
,('F2',NULL,1);Thanks,
Naveen J V
Hai,
try to read the following articles about dynamically pivoting tables:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral
October 30, 2017 at 1:12 am
There are few ways to get the desired output. You can look at this one.
Note: Aded an 'ID' column (Identity) in the result table to ensure order by and as a row identifier.
Here is the complete code:
CREATE TABLE #Results
(
ID int identity(1,1) primary key, ----- Added a identity column and set as a primary key
FieldName nvarchar(50),
FieldValue nvarchar(50),
RecordStaus int
);
INSERT INTO #Results(FieldName,FieldValue,RecordStaus)
VALUES ('Coverage',NULL,1)
,('Premium',NULL,2)
,('F1',100,1)
,('F2',100,1)
,('Coverage',200,1)
,('Premium',10,1)
,('F1',50,1)
,('F2',NULL,3)
,('Coverage',300,1)
,('Premium',45,1)
,('F1',24,1)
,('F2',NULL,1)
,('Coverage',450,3)
,('Premium',12,3)
,('F1',50,1)
,('F2',NULL,1);
-------------- Query
SELECT
a.NTileCol
, MAX(CASE WHEN r.FieldName = 'Coverage' THEN fieldValue END) AS Coverage_Val
, MAX(CASE WHEN r.FieldName = 'Coverage' THEN RecordStaus END) AS Coverage_RecordStatus
, MAX(CASE WHEN r.FieldName = 'Premium' THEN fieldValue END) AS Premium_Val
, MAX(CASE WHEN r.FieldName = 'Premium' THEN RecordStaus END) AS Premium_RecordStatus
, MAX(CASE WHEN r.FieldName = 'F1' THEN fieldValue END) AS F1_Val
, MAX(CASE WHEN r.FieldName = 'F1' THEN RecordStaus END) AS F1_RecordStatus
, MAX(CASE WHEN r.FieldName = 'F2' THEN fieldValue END) AS F2_Val
, MAX(CASE WHEN r.FieldName = 'F2' THEN RecordStaus END) AS F2_RecordStatus
FROM
(
SELECT
Id
, ISNULL(LEAD(ID) OVER (ORDER BY Id) - 1 , maxId) AS nextId
, ROW_NUMBER() OVER (ORDER BY Id) AS NTileCol
FROM
(
SELECT
ID
, MAX(id) OVER () AS maxId
, FieldName
FROM #Results
) r
WHERE FieldName = 'Coverage'
) a
JOIN
#Results r ON r.id >= a.ID AND r.id <= a.nextId
GROUP BY
a.NTileCol
;
--- Clean up
DROP TABLE #Results
;
October 30, 2017 at 1:18 am
twin.devil - Monday, October 30, 2017 1:12 AMThere are few ways to get the desired output. You can look at this one.
Note: Aded an 'ID' column (Identity) in the result table to ensure order by and as a row identifier.
Here is the complete code:
CREATE TABLE #Results
(
ID int identity(1,1) primary key, ----- Added a identity column and set as a primary key
FieldName nvarchar(50),
FieldValue nvarchar(50),
RecordStaus int
);
INSERT INTO #Results(FieldName,FieldValue,RecordStaus)
VALUES ('Coverage',NULL,1)
,('Premium',NULL,2)
,('F1',100,1)
,('F2',100,1)
,('Coverage',200,1)
,('Premium',10,1)
,('F1',50,1)
,('F2',NULL,3)
,('Coverage',300,1)
,('Premium',45,1)
,('F1',24,1)
,('F2',NULL,1)
,('Coverage',450,3)
,('Premium',12,3)
,('F1',50,1)
,('F2',NULL,1);-------------- Query
SELECT
a.NTileCol, MAX(CASE WHEN r.FieldName = 'Coverage' THEN fieldValue END) AS Coverage_Val
, MAX(CASE WHEN r.FieldName = 'Coverage' THEN RecordStaus END) AS Coverage_RecordStatus
, MAX(CASE WHEN r.FieldName = 'Premium' THEN fieldValue END) AS Premium_Val
, MAX(CASE WHEN r.FieldName = 'Premium' THEN RecordStaus END) AS Premium_RecordStatus
, MAX(CASE WHEN r.FieldName = 'F1' THEN fieldValue END) AS F1_Val
, MAX(CASE WHEN r.FieldName = 'F1' THEN RecordStaus END) AS F1_RecordStatus
, MAX(CASE WHEN r.FieldName = 'F2' THEN fieldValue END) AS F2_Val
, MAX(CASE WHEN r.FieldName = 'F2' THEN RecordStaus END) AS F2_RecordStatusFROM
(SELECT
Id
, ISNULL(LEAD(ID) OVER (ORDER BY Id) - 1 , maxId) AS nextId
, ROW_NUMBER() OVER (ORDER BY Id) AS NTileCol
FROM
(
SELECT
ID
, MAX(id) OVER () AS maxId
, FieldName
FROM #Results
) r
WHERE FieldName = 'Coverage'
) a
JOIN
#Results r ON r.id >= a.ID AND r.id <= a.nextId
GROUP BY
a.NTileCol
;
--- Clean up
DROP TABLE #Results
;
Hi Twin,
Your solution is working fine, but we cannot hardcode FieldName column values, because it is dynamic.
Regards,
Naveen J V
October 30, 2017 at 1:26 am
Well, this can be achieved through dynamic sql. All you need to do it to construct the select CASE portion of the query with dynamic column list you have in fieldName.
Check out the links shared by Deny Christian above.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy