求Sql 语句的写法

Question

表名    :    tb1
字段名称:   ID        a
字段值  :   1 10667766*X06*KYG08B01L18XU01*V4_10667766*X06*KYG08B01L18XU01*V4
   2 10667766*X06*KYG01TSTSHELU01_10667766*A53*KYG01TSTSHELU01_10667766*X06*KYG01TSTSHELU01*V4
   3 10668001*2KY*KYM01B01L18XU01*V4
   4  1062376664*4*KYG08B01L18XU01*V4_1062376664*4*KYG08B01L18XU01*V4
怎么把上表变成如下所示:ID    a1                                       a2                                a3
1  10667766*X06*KYG08B01L18XU01*V4   10667766*X06*KYG08B01L18XU01*V4
2  10667766*X06*KYG01TSTSHELU01      10667766*A53*KYG01TSTSHELU01        10667766*X06*KYG01TSTSHELU01*V4
3  10668001*2KY*KYM01B01L18XU01*V4
4  1062376664*4*KYG08B01L18XU01      KYG08B01L18XU01*V4                  1062376664*4*KYG08B01L18XU01*V4

Answer 1

Don't understand the logic. You need to provide more information as to how you arrive from the original table to the output you provided.

Answer 2

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[a] [nvarchar](90))
INSERT INTO [tb]
SELECT '1','10667766*X06*KYG08B01L18XU01*V4_10667766*X06*KYG08B01L18XU01*V4' UNION ALL
SELECT '2','10667766*X06*KYG01TSTSHELU01_10667766*A53*KYG01TSTSHELU01_10667766*X06*KYG01TSTSHELU01*V4' UNION ALL
SELECT '3','10668001*2KY*KYM01B01L18XU01*V4' UNION ALL
SELECT '4','1062376664*4*KYG08B01L18XU01*V4_1062376664*4*KYG08B01L18XU01*V4'--SELECT * FROM [tb]-->SQL查询如下:
;WITH t AS 
(
SELECT m.id, n.[a] ,rn='a'+LTRIM(ROW_NUMBER()OVER(PARTITION BY m.id ORDER BY m.id))
FROM (
 SELECT id, [a] = CONVERT(XML, '<v>'+REPLACE([a], '_', '</v><v>')+'</v>') 
 FROM tb
 ) m
OUTER APPLY(
SELECT [a] = n.v.value('.', 'varchar(10)') 
FROM m.[a].nodes('/v') n(v)
) n
) 
SELECT * 
FROM t 
     PIVOT(MAX(a) FOR rn IN (a1, a2, a3)) p
/*
id          a1         a2         a3
----------- ---------- ---------- ----------
1           10667766*X 10667766*X NULL
2           10667766*X 10667766*A 10667766*X
3           10668001*2 NULL       NULL
4           1062376664 1062376664 NULL(4 行受影响)
*/

Answer 3

－－动态SQL：--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[a] [nvarchar](90))
INSERT INTO [tb]
SELECT '1','10667766*X06*KYG08B01L18XU01*V4_10667766*X06*KYG08B01L18XU01*V4' UNION ALL
SELECT '2','10667766*X06*KYG01TSTSHELU01_10667766*A53*KYG01TSTSHELU01_10667766*X06*KYG01TSTSHELU01*V4' UNION ALL
SELECT '3','10668001*2KY*KYM01B01L18XU01*V4' UNION ALL
SELECT '4','1062376664*4*KYG08B01L18XU01*V4_1062376664*4*KYG08B01L18XU01*V4'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @s VARCHAR(MAX)
SELECT @s = STUFF(
           (
               SELECT TOP(
                          SELECT MAX(LEN(a)-LEN(REPLACE(a, '_', '')))+1 
                          FROM tb
                      ) ',a'+LTRIM(ROW_NUMBER()OVER(ORDER BY id)) 
               FROM sysobjects FOR XML PATH('')
           ), 1, 1, ''
       )
EXEC('
WITH t AS 
(
SELECT m.id, n.[a] ,rn=''a''+LTRIM(ROW_NUMBER()OVER(PARTITION BY m.id ORDER BY m.id))
FROM (
 SELECT id, [a] = CONVERT(XML, ''<v>''+REPLACE([a], ''_'', ''</v><v>'')+''</v>'') 
 FROM tb
 ) m
OUTER APPLY(
SELECT [a] = n.v.value(''.'', ''varchar(100)'') --长度可根据实际修改
FROM m.[a].nodes(''/v'') n(v)
) n
) 
SELECT * 
FROM t 
 PIVOT(MAX(a) FOR rn IN ('+@s+')) p
')
/*
id          a1                             a2                             a3
----------- ------------------------------ ------------------------------ ------------------------------
1           10667766*X06*KYG08B01L18XU01*V 10667766*X06*KYG08B01L18XU01*V NULL
2           10667766*X06*KYG01TSTSHELU01   10667766*A53*KYG01TSTSHELU01   10667766*X06*KYG01TSTSHELU01*V
3           10668001*2KY*KYM01B01L18XU01*V NULL                           NULL
4           1062376664*4*KYG08B01L18XU01*V 1062376664*4*KYG08B01L18XU01*V NULL(4 行受影响)*/