Разделение значений строк на столбцы-tsql

Я искал SQL Server: строки в столбцы для разделения значений строк на столбцы, и это мне не помогло

У меня есть образцы данных

Col1
0183125031218
13020690831
79100146868
0183225031138
13010690834
79100145497

Ожидаемый результат:

Col1             Col2             Col3 
0183125031128    13020690831      79100146868
0183225031138    13010690834      79100145497

Моя попытка:

Я пробовал работать с Row_number, но не получил требуемого результата. Поскольку мне нужно условное разделение left(col,2) = 01 на первый столбец и left(col,2)=13 на второй столбец. На основе первого столбца может быть назначен номер строки. Нет фиксированной длины ни для одной строки данных

Приветствую любую помощь или предложения

а на col3 должны быть все остальные номера?

Zohar Peled 11.04.2018 12:26

@ZoharPeled Col3 на основе left(col,2) = 79

Hadrian 11.04.2018 12:27

Что определяет отношения между 0183125031128 и 13020690831? Просто они самые низкие числа в своих группах? Если добавить значение 0183125031217, будет ли это иметь отношение к 13020690831?

Larnu 11.04.2018 12:32

@larnu Между ними нет никакой связи, это просто наименьшее число в качестве идентификатора для групп

Hadrian 11.04.2018 12:37

Я думаю, вам нужно пересмотреть свой подход, если нет связи между столбцами для определенной записи. Возможно, вы захотите поместить связанные значения в отдельную таблицу.

ppijnenburg 11.04.2018 12:47

Откуда взялось 13010690834?

paparazzo 11.04.2018 12:59

@paparazzo, это транзакция, и left (col, 2) имеет уникальное значение, данные попадают в промежуточную таблицу таким образом. Я отредактировал входной образец, добавив 4 в конце

Hadrian 11.04.2018 13:03

Но этого нет в постановочной таблице. Правила для трех столбцов мне до сих пор непонятны.

paparazzo 11.04.2018 13:04

@paparazzo Зачем нужно голосовать против?

Hadrian 11.04.2018 15:49

Был не я. Вы хотите, чтобы я проголосовал против, чтобы доказать это.

paparazzo 11.04.2018 15:50

@paparazzo Вы можете проголосовать, если считаете, что вопрос полезен для других, и респонденты на него хорошо ответили. У меня есть хорошие ответы от всех и найдено рабочее решение. Я не могу проголосовать за вас, потому что это не дает мне возможности сделать это

Hadrian 11.04.2018 16:15

@ Адриан Не достоин голоса "за". Неясная постановка вопроса.

paparazzo 11.04.2018 16:28

@paparazzo Не проблема. Мы все здесь, чтобы учиться и делиться своими знаниями, это конечная цель. В любом случае цените ваши комментарии.

Hadrian 11.04.2018 16:32
0
13
814
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

Ответ принят как подходящий

Попробуйте следующий запрос

-- test data
CREATE TABLE TestData(Col1 varchar(20))

INSERT TestData(Col1)VALUES
('0183125031218'),
('13020690831'),
('79100146868'),
('0183225031138'),
('1301069083'),
('79100145497')

-- query
SELECT
  COALESCE(q1.N,q2.N,q3.N) RowNum,
  q1.Col1,
  q2.Col1 Col2,
  q3.Col1 Col3
FROM
  (
    SELECT ROW_NUMBER()OVER(ORDER BY Col1) N,Col1
    FROM TestData
    WHERE Col1 LIKE '01%'
  ) q1
FULL JOIN
  (
    SELECT ROW_NUMBER()OVER(ORDER BY Col1) N,Col1
    FROM TestData
    WHERE Col1 LIKE '13%'
  ) q2
ON q2.N=q1.N
FULL JOIN
  (
    SELECT ROW_NUMBER()OVER(ORDER BY Col1) N,Col1
    FROM TestData
    WHERE Col1 LIKE '79%'
  ) q3
ON q3.N=COALESCE(q1.N,q2.N)

И вариант с PIVOT

SELECT *
FROM
  (
    SELECT
      LEFT(Col1,2) RowType,
      ROW_NUMBER()OVER(PARTITION BY LEFT(Col1,2) ORDER BY Col1) N,
      Col1
    FROM TestData
  ) q PIVOT(MAX(Col1) FOR RowType IN([01],[13],[79])) p

Thats Perfect, спасибо, только amedment с pivot, поскольку он возвращает много строк с нулевыми значениями, я устраню нулевые значения с помощью cte. Спасибо

Hadrian 11.04.2018 12:51

Как насчет использования CTE для присвоения номера строки и группировки?

CREATE TABLE #Sample (Col1 varchar(50));
GO
INSERT INTO #Sample
VALUES ('0183125031218'),
       ('13020690831'),
       ('79100146868'),
       ('0183225031138'),
       ('1301069083'),
       ('79100145497');
GO

SELECT *
FROM #SAmple;

WITH Grp AS(
    SELECT Col1,
           LEFT(Col1,2) AS Grp,
           ROW_NUMBER() OVER (PARTITION BY LEFT(Col1,2) ORDER BY Col1) AS RN
    FROM #Sample)
SELECT G1.Col1 AS Col1,
       G2.Col1 AS Col2,
       G3.Col1 AS Col3
FROM Grp G1
     JOIN Grp G2 ON G1.RN = G2.RN
                AND G2.Grp = '13'
     JOIN Grp G3 ON G1.RN = G3.RN
                AND G3.Grp = '79'
WHERE G1.Grp = '01';
GO

DROP TABLE #Sample;

Это делает запрос более кратким / короче, чем использование UNION в ответе leran2002.

Попробуй это. Идея состоит в том, чтобы разделить таблицу на три столбца в зависимости от заданного условия, а затем объединить их обратно на основе row_number() без какого-либо упорядочивания, поскольку вы сказали, что нет никакой связи между конкретными записями.

declare @table table(col1 varchar(100))
insert into @table values
('0183125031218'),
('13020690831'),
('79100146868'),
('0183225031138'),
('1301069083'),
('79100145497')

select [col1],[col2],[col3] from
--IMPORTANT: here should go the query, that will have the most records!!!
(select col1 [col1], ROW_NUMBER() over (order by (select null)) [rn1] from @table where LEFT(col1, 2) = '01') [c1]
left join
(select col1 [col2], ROW_NUMBER() over (order by (select null)) [rn2] from @table where LEFT(col1, 2) = '13') [c2]
on [rn1]=[rn2] left join
(select col1 [col3], ROW_NUMBER() over (order by (select null)) [rn3] from @table where not LEFT(col1, 2) in ('01','13')) [c3]
on [rn1]=[rn3]

То же, что и Ларну (+1)
Я просто хотел проверить это

declare @T TABLE (col varchar(50));
INSERT INTO @T
VALUES ('0183125031218'),
       ('13020690831'),
       ('79100146868'),
       ('0183225031138'),
       ('13010690834'),
       ('79100145497');
SELECT *
FROM @T;

WITH cte AS
( SELECT col, LEFT(col,2) AS grp, 
         ROW_NUMBER() OVER (PARTITION BY LEFT(col,2) ORDER BY col) AS rn
    FROM @T  
    where LEFT(col,2) in ('01', '13', '79')
)
SELECT G1.col AS Col1,
       G2.col AS Col2,
       G3.col AS Col3
FROM cte G1
JOIN cte G2 ON G1.RN = G2.RN
           and G1.Grp = '01'
           AND G2.Grp = '13'
JOIN cte G3 ON G1.RN = G3.RN
           AND G3.Grp = '79'
order by G1.col, G2.col, G3.col;

Другие вопросы по теме