SQLTeam.com | Weblogs | Forums

Need to transform ( un-pivot) data in SQL


#1

Hi Friends,

I am new in SQL and we have a table with below data. I have a requirement to refine below data in a table structure like VIEW or Materialized VIEW . Please help on how to achieve it. I have listed Management chain in BOLD and Italic to distinguish from other data.

Employee Manager Management_Chain
vp2221 bg7915 jd613hbs0712sm2389mw6143mc7580ab3793bg7915vp2221
bg7915 ab3793 jd613hbs0712sm2389mw6143mc7580ab3793bg7915
ab3793 mc7580 jd613hbs0712sm2389mw6143mc7580ab3793
rx2131 bg7915 jd613hbs0712sm2389mw6143mc7580ab3793bg7915rx2131

The ask is to convert above data as below... adding a distance column values ( 0, 1,2,3,4,....) based on Employee and Management_Chain column. For each employee there is a management chain where employee id is also part of Mgmt chain.

Employee Manager Distance
vp2221 vp2221 0
vp2221 bg7915 1
vp2221 ab3793 2
vp2221 mc7580 3
vp2221 mw6143 4
vp2221 sm2389 5
vp2221 bs0712 6
vp2221 jd613h 7
bg7915 bg7915 0
bg7915 ab3793 1
bg7915 mc7580 2
bg7915 mw6143 3
bg7915 sm2389 4
bg7915 bs0712 5
bg7915 jd613h 6
ab3793 ab3793 0
ab3793 mc7580 1
ab3793 mw6143 2
ab3793 sm2389 3
ab3793 bs0712 4
ab3793 jd613h 5
rx2131 vp2221 0
rx2131 bg7915 1
rx2131 ab3793 2
rx2131 mc7580 3
rx2131 mw6143 4
rx2131 sm2389 5
rx2131 bs0712 6
rx2131 jd613h 7

Thank you for your help in Advance


#2

I would recommend posting your question on a site that specializes in Oracle. SQLTeam.com is primarily for Microsoft SQL Server, though maybe someone will answer your question here.


#3

Hello SQL Gurus,

could someone answer my questions. It's kind of urgent. Thanks.


#4

Are you using Microsoft SQL Server? If you are what is the version of SQL Server?


#5

SQL server 2008


#6

Ah you edited it so that it doesn't say Oracle anymore!


#7

Posting data like that doesn't help us help you. Please post your data like the following in the future so that we can test our stuff when we try to post an answer. Otherwise, the answer could be just words like "This is easy if you have a Tally Table or Tally function and use it to parse every six characters from the Management_Chain and number them in reverse with ROW_NUMBER and PARTITION BY".

Here's how to post the data so that it's readily consumable for people that want to help but don't want to have to build test data.

--==========================================================================
--      Create and populate a test table to help those that would help you.
--==========================================================================
 CREATE TABLE #TestTable
        (
         Employee           CHAR(6)
        ,Manager            CHAR(6)
        ,Management_Chain   VARCHAR(1000)
        )
;
 INSERT INTO #TestTable
        (Employee,Manager,Management_Chain)
 SELECT 'vp2221','bg7915','jd613hbs0712sm2389mw6143mc7580ab3793bg7915vp2221' UNION ALL
 SELECT 'bg7915','ab3793','jd613hbs0712sm2389mw6143mc7580ab3793bg7915'       UNION ALL
 SELECT 'ab3793','mc7580','jd613hbs0712sm2389mw6143mc7580ab3793'             UNION ALL
 SELECT 'rx2131','bg7915','jd613hbs0712sm2389mw6143mc7580ab3793bg7915rx2131'
;

Next, build the following function. If your DBA squawks about it because it's a function, tell him it's neither a scalar (Scalar UDF) nor a multi-statement table valued function (mTVF) and that it's a very high performance, no read, inline Table Valued Function (iTVF). If you or he want to know more about why you need such a function, it's a very high performance method of replacing certain types of While loops and slothful recursive CTEs (rCTE) that counts.

/****** Object:  UserDefinedFunction [dbo].[fnTally]    Script Date: 06/21/2015 22:48:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.

 As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

 Usage:
--===== Syntax example (Returns BIGINT)
 SELECT t.N
   FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;

 Notes:
 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URLs for how it works and introduction for how it replaces certain loops. 
    http://www.sqlservercentral.com/articles/T-SQL/62867/
    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
    will cause the sequence to start at 1.
 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
    number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
    that many values, you should consider using a different tool. ;-)
 7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending 
    sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
    very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally(1,@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 22 Apr 2015 - Jeff Moden
        - Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1)                                  --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)      --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c)            --10E12 or 1 Trillion rows                 
            SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
             UNION ALL 
            SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;

From there, problems like the one you have posed become child's play...

--===== Solve the problem in a set-based manner
 SELECT  tt.Employee
        ,Manager    = SUBSTRING(tt.Management_Chain,t.N*6+1,6)
        ,Distance   = ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY t.N DESC)-1
   FROM #TestTable tt
  CROSS APPLY dbo.fnTally(0,LEN(tt.Management_Chain)/6-1) t
  ORDER BY tt.Employee, Distance
;