October 18, 2010 at 1:06 pm
I have a table that I need to manipulate in a different way. I would ultimately like to re-write the table but I don't have the time to be able to re-write everything that it impacts.
I have a table that we created to house our security. It was designed a long time ago and now it is so ingrained in everything that its hard to re-write things. What I was wondering if I could do is there a way in T-SQL that I could take the data from this table:
CREATE TABLE [dbo].[GblSecurityMain](
[EmployeeNumber] [int] NOT NULL,
[Development] [int] NOT NULL,
[Time] [int] NOT NULL,
[Inventory] [int] NOT NULL,
[Renewal] [int] NOT NULL,
[Support] [int] NOT NULL,
[Results] [int] NOT NULL
)
INSERT INTO GblSecurityMain
(EmployeeNumber, Development, Time, Inventory, Renewal, Support,Results)
VALUES (1234,1,1,1,1,2,3),
(1232,1,1,1,1,2,3),
(1212,1,4,1,1,2,3),
(1235,1,1,3,1,2,3)
That gives a result of this:
EmployeeNumber|Development|Time|Inventory|Renewal|Support|Results
1234 1 1 1 1 2 3
1232 1 1 1 1 2 3
1212 1 4 1 1 2 3
1235 1 1 3 1 2 3
and change it give a result that's more like this:
Employee Number|Security|Level
1234|Development|1
1234|Time|1
1234|Inventory|1
1234|Renewal|1
any help on this would be great.
October 18, 2010 at 3:12 pm
You're looking for Unpivot.
A quick fix to the sample data:
CREATE TABLE [dbo].[#GblSecurityMain](
[EmployeeNumber] [int] NOT NULL,
[Development] [int] NOT NULL,
[Time] [int] NOT NULL,
[Inventory] [int] NOT NULL,
[Renewal] [int] NOT NULL,
[Support] [int] NOT NULL,
[Results] [int] NOT NULL
)
INSERT INTO #GblSecurityMain (EmployeeNumber, Development, Time, Inventory, Renewal, Support,Results) VALUES (1234,1,1,1,1,2,3)
INSERT INTO #GblSecurityMain (EmployeeNumber, Development, Time, Inventory, Renewal, Support,Results) VALUES (1232,1,1,1,1,2,3)
INSERT INTO #GblSecurityMain (EmployeeNumber, Development, Time, Inventory, Renewal, Support,Results) VALUES (1212,1,4,1,1,2,3)
INSERT INTO #GblSecurityMain (EmployeeNumber, Development, Time, Inventory, Renewal, Support,Results) VALUES (1235,1,1,3,1,2,3)
GO
Values needs a separate insert into statement. Now, the fix (please note I swapped it to a #tbl):
SELECt
EmployeeNumber,
Task,
StatusNum
FROM
(SELECT EmployeeNumber, Development, [Time], Inventory, Renewal, Support, Results
FROM #GblSecurityMain) AS gsm
UNPIVOT
(StatusNum FOR Task IN
(Development, [Time], Inventory, Renewal, Support, Results)
) AS unpvt
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 3:12 pm
UNPIVOT does precisely what you're looking for.
SELECT [EmployeeNumber], [Security], [Level]
FROM [dbo].[GblSecurityMain]
UNPIVOT (
[Level] FOR [Security]
IN ([Development], [Time], [Inventory], [Renewal], [Support], [Results])
) AS UNPVT
Snap!
Re:
Values needs a separate insert into statement
Craig - inserting multiple rows with VALUES is a new feature in SQL Server 2008 - see "Table Value Constructor".
October 18, 2010 at 3:42 pm
This is why i love this site, this is exactly what i need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply