有两列数据，需要合并一下
col1
001
002
006
col2
99.065
31.083
需要实现的效果是这个：
col1 col2
001 99.065
002
006 31.083
中间有一个是空格，没有数据，要一一对应，求大大们帮一下
其实就是这一列数据变成那个效果
SET @H='001,002,006$99.065| |31.083'
上面的是我已经处理到那一步了
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-09 16:22:57
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([col1] nvarchar(6))
insert [A]
select '001' union all
select '002' union all
select '006'
if object_id('[B]') is not null drop table [B]
go
create table [B]([col2] numeric(5,3))
insert [B]
select 99.065 union ALL
SELECT NULL union ALL
select 31.083
--------------生成数据--------------------------
SELECT a.col1,b.col2
FROM (
select ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP)id,* from [B]) b INNER JOIN (
select ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP)id,*
from [A])a ON a.id = b.id
----------------结果----------------------------
/*
col1 col2
------ ---------------------------------------
001 99.065
002 NULL
006 31.083
*/
------解决方案--------------------
declare @str varchar(100)= ',001,002,006'
select f.str_list,ROW_NUMBER() over(order by number) list_no from (
select substring(@str,number+2,charindex(',',@str+',',number+2)-number-2) str_list,number
from master..spt_values
where substring(','+@str,number+2,8000) like ',_%' and number<=len(@str)
and type='p'
)f
函数主体在这，上面的那个有错