December 4, 2015 at 1:18 pm
Hello,
I have a table in the following structure:
CREATE TABLE [dbo].[WeeklySummary](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PolicyID] [int] NULL,
[Category] [varchar](50) NULL,
[High] [int] NULL,
[MedLow] [int] NULL,
[EndPoint] [int] NULL,
[TotalReviewed] [int] NULL,
[TotalNotReviewed] [int] NULL,
[UserAdvised] [int] NULL,
[Escalated] [int] NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[CreatedOn] [datetime] NOT NULL
)
For reporting purpose, I need the output result be:
[ID] [int] IDENTITY(1,1) NOT NULL,
[Category] [varchar](50) NULL,
[Severity] [varchar](50) NULL,
[Value] [int] NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[CreatedOn] [datetime] NOT NULL
Severity will indicate High, MedLow, ...., Escalated
Can anyone help output the original table to the new table structure, I know how to do it via cursor or temp table, I am wondering if there is better way to transform it with single Select query?
Thank you very much.
December 4, 2015 at 1:26 pm
What have you tried? Where's the sample data and expected results?
December 4, 2015 at 2:00 pm
Thanks.
No, I didn't try as I don't know how to do it in a single query, I know for sure I can get it done using a stored procedure with cursor or temp table and loop the original table and transform it, original table's content is like below:
IDPolicyIDCategoryHighMedLowEndPointTotalReviewedTotalNotReviewedUserAdvisedEscalatedCreatedOn
11PII123456711/6/2015
22Confidential234567811/6/2015
33PCI DSS345678911/6/2015
44Inappropriate4567891011/6/2015
55Others56789101111/6/2015
66RemovableMedia678910111211/6/2015
the expected result would be like:
IDCategorySeverityValueCreatedOn
1PIIHigh111/6/2015
2PIIMedLow211/6/2015
3PIIEndPoint311/6/2015
4PIITotalReviewed411/6/2015
5PIITotalNotReviewed511/6/2015
6PIIUserAdvised611/6/2015
7PIIEscalated711/6/2015
8ConfidentialHigh211/6/2015
9ConfidentialMedLow311/6/2015
10ConfidentialEndPoint411/6/2015
11ConfidentialTotalReviewed511/6/2015
12ConfidentialTotalNotReviewed611/6/2015
13ConfidentialUserAdvised711/6/2015
14ConfidentialEscalated811/6/2015
December 4, 2015 at 2:01 pm
Sorry the data looks so messy after it was posted. You got the idea though.
December 4, 2015 at 2:10 pm
Now I see what's going on. Since you didn't post your sample data in a consumable way even if you've been around a long time, I'll just point you to the place where you'll find the information.
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
And because I just posted it in another thread, here's a generic example:
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (colx VARCHAR(8), coly VARCHAR(8), colz VARCHAR(8))
INSERT INTO #test
VALUES ('Alpha', 'Delta', 'Bravo')
--Common unpivot
SELECT ColName, SomeValue
FROM (SELECT * FROM #test) AS x
UNPIVOT (SomeValue FOR ColName IN (colx, coly, colz)) up;
--Alternative static
SELECT ColName, SomeValue
FROM #test
CROSS APPLY( VALUES('colx', colx),
('coly', coly),
('colz', colz)) u(ColName, SomeValue);
December 4, 2015 at 2:34 pm
Thank you very much. For people who read the post in the future, the final script is actually very simple and working:
SELECT Category, ColName, Value
FROM (SELECT * FROM [WeeklySummary]) AS x
UNPIVOT (Value FOR ColName IN (High, MedLow, EndPoint, TotalReviewed, TotalNotReviewed, UserAdvised, Escalated)) up;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply