Geek asks and answers

How to change the information in this table in a convenient form?

I have an outdated product that I have to support. One of the tables is somewhat similar to the following example:

DECLARE @t TABLE
(
 id INT,
 DATA NVARCHAR(30)
);

INSERT  INTO @t
        SELECT  1,
                'name: Jim Ey'
        UNION ALL
        SELECT  2,
                'age: 43'
        UNION ALL
        SELECT  3,
                '----------------'
        UNION ALL
        SELECT  4,
                'name: Johnson Dom'
        UNION ALL
        SELECT  5,
                'age: 34'
        UNION ALL
        SELECT  6,
                '----------------'
        UNION ALL
        SELECT  7,
                'name: Jason Thwe'
        UNION ALL
        SELECT  8,
                'age: 22'

SELECT  *
FROM    @t;
/*
You will get the following result
id          DATA
----------- ------------------------------
1           name: Jim Ey
2           age: 43
3           ----------------
4           name: Johnson Dom
5           age: 34
6           ----------------
7           name: Jason Thwe
8           age: 22
*/

Now I want to get the information in the following form:

name           age
-------------- --------
Jim Ey         43
Johnson Dom    34
Jason Thwe     22

What is the easiest way to do this? Thank.

+3
source share
4 answers

Out of (slightly painful) curiosity, I tried to come up with a way to convert the exact input that you provided.

It would be much better, of course, to properly structure the source data. With an outdated system, this may not be possible, but an ETL process can be created to bring this information to an intermediate location, to unreasonably launch an ugly request in real time.

# 1

, ( ROW_NUMBER() ).

SELECT
    Name = REPLACE( Name, 'name: ', '' ),
    Age = REPLACE( Age, 'age: ', '' )
FROM
(
    SELECT
        Name = T2.Data,
        Age = T1.Data,
        RowNumber = ROW_NUMBER() OVER( ORDER BY T1.Id ASC )

    FROM @t T1 
        INNER JOIN @t T2 ON T1.id = T2.id +1 -- offset by one to combine two rows
    WHERE T1.id % 3 != 0 -- skip delimiter records
) Q1
 -- skip every other record (minus delimiters, which have already been stripped)
WHERE RowNumber % 2 != 0

# 2:

, , .

DECLARE @NumberedData TABLE( RowNumber INT, Data VARCHAR( 100 ) );

INSERT @NumberedData( RowNumber, Data )
    SELECT 
        RowNumber = ROW_NUMBER() OVER( ORDER BY id ASC ),
        Data
    FROM @t;

SELECT 
    Name = REPLACE( N2.Data, 'name: ', '' ),
    Age = REPLACE( N1.Data, 'age: ', '' ) 
FROM @NumberedData N1 
    INNER JOIN @NumberedData N2 ON N1.RowNumber = N2.RowNumber + 1
WHERE ( N1.RowNumber % 3 ) = 2;

DELETE @NumberedData;

# 3:

, ETL. , , , .

# 1 # 2 ( , ) , . ? ( , , ), , .

-- this could be a table variable, temp table, or staging table
DECLARE @Results TABLE ( Name VARCHAR( 100 ), Age INT );

DECLARE @Index INT = 0, @Data VARCHAR( 100 ), @Name VARCHAR( 100 ), @Age INT;

DECLARE Person_Cursor CURSOR FOR SELECT Data FROM @t;
OPEN Person_Cursor;
FETCH NEXT FROM Person_Cursor INTO @Data;

WHILE( 1 = 1 )BEGIN -- busy loop so we can handle the iteration following completion
    IF( @Index = 2 ) BEGIN
        INSERT @Results( Name, Age ) VALUES( @Name, @Age );
        SET @Index = 0;
    END
    ELSE BEGIN
            -- optional: examine @Data for integrity

        IF( @Index = 0 ) SET @Name = REPLACE( @Data, 'name: ', '' );
        IF( @Index = 1 ) SET @Age = CAST( REPLACE( @Data, 'age: ', '' ) AS INT );
        SET @Index = @Index + 1;
    END

    -- optional: examine @Index to see that there are no superfluous trailing 
    -- rows or rows omitted at the end.

    IF( @@FETCH_STATUS != 0 ) BREAK;
    FETCH NEXT FROM Person_Cursor INTO @Data;
END

CLOSE Person_Cursor;
DEALLOCATE Person_Cursor;

100K , .

, , , (, - ).

-- INT IDENTITY( 1, 1 ) numbers the rows for us
DECLARE @NumberedData TABLE( RowNumber INT IDENTITY( 1, 1 ), Data VARCHAR( 100 ) );

-- subset selection; ordering/filtering can be done here but it will need to preserve
-- the original 3 rows-per-result structure and it will impact performance
INSERT @NumberedData( Data )
    SELECT TOP 1000 Data FROM @t;

SELECT
    N1.RowNumber,
    Name = REPLACE( N2.Data, 'name: ', '' ),
    Age = REPLACE( N1.Data, 'age: ', '' ) 
FROM @NumberedData N1 
    INNER JOIN @NumberedData N2 ON N1.RowNumber = N2.RowNumber + 1
WHERE ( N1.RowNumber % 3 ) = 2;

DELETE @NumberedData;

4-10 (i7-3960x) .

+4

:

;WITH DATA
AS
(
    SELECT
        SUBSTRING(t.DATA,CHARINDEX(':',t.DATA)+2,LEN(t.DATA)) AS value,
        SUBSTRING(t.DATA,0,CHARINDEX(':',t.DATA)) AS ValueType,
        ID,
        ROW_NUMBER() OVER(ORDER BY ID) AS RowNbr
    FROM
        @t AS t
    WHERE
        NOT t.DATA='----------------'
)
, RecursiveCTE
AS
(
    SELECT
        Data.RowNbr,
        Data.value,
        Data.ValueType,
        NEWID() AS ID
    FROM
        Data
    WHERE
        Data.RowNbr=1
    UNION ALL
    SELECT
        Data.RowNbr,
        Data.value,
        Data.ValueType,
        CASE 
            WHEN Data.ValueType='age'
            THEN RecursiveCTE.ID
            ELSE NEWID()
        END AS ID
    FROM
        Data
        JOIN RecursiveCTE
            ON RecursiveCTE.RowNbr+1=Data.RowNbr
)
SELECT
    pvt.name,
    pvt.age
FROM
    (
        SELECT
            ID,
            value,
            ValueType
        FROM
            RecursiveCTE
    ) AS SourceTable
    PIVOT
    (
        MAX(Value)
        FOR ValueType IN ([name],[age])
    ) AS pvt


Name          Age
------------------
Jim Ey        43
Jason Thwe    22
Johnson Dom   34
+1

, @t:

SELECT  *
FROM
(
        SELECT  
                CASE 
                    WHEN a.DATA LIKE 'name:%' THEN 'Name'
                    ELSE 'Age'
                END AS Attribute,
                CASE 
                    WHEN a.DATA LIKE 'name:%' THEN SUBSTRING(a.DATA, 7, 4000) --or LTRIM(SUBSTRING(...,6,...))
                    ELSE SUBSTRING(a.DATA, 6, 4000) --or LTRIM(SUBSTRING(...,5,...))
                END AS Value,
                (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 AS PseudoDenseRank
        FROM    @t a
        WHERE   a.DATA LIKE 'name:%' OR a.DATA LIKE 'age:%'
) b
PIVOT( MAX(b.Value) FOR b.Attribute IN ([Name], [Age]) ) pvt

:

PseudoDenseRank Name        Age
--------------- ----------- ---
1               Jim Ey      43
2               Johnson Dom 34
3               Jason Thwe  22

1: b name:% age:%, (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2. b:

Attribute Value       ROW_NUMBER() OVER(ORDER BY id) PseudoDenseRank
--------- ----------- ------------------------------ ---------------
Name      Jim Ey      1                              1
Age       43          2                              1
Name      Johnson Dom 3                              2
Age       34          4                              2
Name      Jason Thwe  5                              3
Age       22          6                              3

2: , id (, (id 1, : Jim Ey), (id 3 age: 43)), (a.id + 1) / 2 AS PseudoDenseRank (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 AS PseudoDenseRank.

3: , (a.id + 1) / 2 AS PseudoDenseRank solution ( ), id . id , a.id / 2 AS PseudoDenseRank.

+1

, SQL Server 2012, OVER . , , , , , .

, , .

with Ready2Pivot(tag,val,part) as (
  select
    CASE WHEN DATA like '_%:%' THEN SUBSTRING(DATA,1,CHARINDEX(':',DATA)-1) END as tag,
    CASE WHEN DATA like '_%:%' THEN SUBSTRING(DATA,CHARINDEX(':',DATA)+1,8000) END as val,
    max(id * CASE WHEN DATA LIKE 'name:%' THEN 1 ELSE 0 END)
    over (
      order by id
    )
  from @t
  where DATA like '_%:%'
)
  select [name], [age]
  from Ready2Pivot
  pivot (
    max(val)
    for tag in ([name], [age])
  ) as p

If your legacy data has a record with additional elements (for example, "altName: Jimmy"), this request will ignore it. If your legacy data does not have a string (and no identification number) for someone of age, it will give you NULL at that location. It will associate all the information with the nearest preceding line with the name "..." as DATA, so it is important that each group of lines has the line "name: ...".

0
source

More articles:


All Articles