[SQL Server]列方向のデータ結合

Question

以下のカンマ区切りのデータを使って列方向のデータを結合したテーブルを作りたいです。
※使用しているものは SQL Server 2012 です。

【カンマ区切りデータ】
declare @IDs varchar(100) = '1,2,3'--商品ID
declare @Quantities varchar(100) = '10,20,30'--上記商品IDに対応する数

【最終的に欲しいテーブル】

商品ID	数
1	10
2	20
3	30

【やったこと】
カンマ区切りデータから2つのテーブルを作る（カンマ区切り -> テーブル にする関数を作成）
※下記、上手く表示できておりませんが。。。

商品ID
1
2
3

数
10
20
30

レコード数分のループを回して、各テーブルのループ数分のレコードを取得して、
その結果をインサートする方法は思いついたのですが、もっとスマートな方法があるだろうと思い質問しました。
※列同士がくっつけばいいだけなのに。。。

declare @IDs varchar(100) = '1,2,3'--商品ID
declare @Quantities varchar(100) = '10,20,30'--上記商品IDに対応する数

declare @TID table (nID int)
declare @TQuantity table (nQuantity int)
declare @TOrder table (
nID int,
nQuantity int
)

insert into @TID
select cast(A.str as int) from カンマ区切り -> テーブル にする関数 as A

declare @CountID int = (select count(*) from @TID)

insert into @TQuantity
select cast(B.str as int) from カンマ区切り -> テーブル にする関数 as B

declare @CountQuantity int = (select count(*) from @TQuantity)

if @CountID <> @CountQuantity
begin
--error
select 'error'
end

declare @Count int = 1

while @Count <= @CountID
begin

insert into @TOrder values (
	(select nID from (
		select
			nID,
			row_number() over (order by nID) as RowNumID
		from @TID
	) as C
	where RowNumID = @Count),
	(select nQuantity from (
		select
			nQuantity,
			row_number() over (order by nQuantity) as RowNumQuantity
		from @TQuantity
	) as D
	where RowNumQuantity = @Count)
)

set @Count = @Count + 1

end

-- 最終的に欲しい形にはなったが・・・
select * from @TOrder

Answer 1

カンマ区切り -> テーブル にする関数 がすでにあるなら、それが返すテーブルに row_number() などで適当なIDを付けて、その付けたID同士で join するのが簡単ですかねぇ。

select
    T1.nID,
    T2.nQuantity
from (
    select
      row_number() over (order by cast(A.str as int)) rownum,
      cast(A.str as int) nID
    from [カンマ区切り -> テーブル にする関数](@IDs, ',') A
) T1 inner join (
    select
      row_number() over (order by cast(B.str as int)) rownum,
      cast(B.str as int) nQuantity
    from [カンマ区切り -> テーブル にする関数](@Quantities, ',') B
) T2 on T1.rownum=T2.rownum

※ カンマ区切り -> テーブル にする関数 がないので動作確認できていません

Comments

[bose]

ご回答ありがとうございます！
すごく為になりました！

Answer 2

コードはブロックで括ってくださいね。
「なんの情報も無しに結合する」というコト自体が「データベース的な考えではない」と思います。

declare @IDs varchar(100) = '1,2,3'--商品ID
declare @Quantities varchar(100) = '10,20,30'--上記商品IDに対応する数

そもそもこういう風になっている事自体が間違いだと思います。

declare @Datas varchar(MAX) = '1@10,2@20,3@30'

最悪こんな感じで「ID[1]が[10]個ある」みたいな感じで
ある程度関連性を持たせたデータにすべきだと思います。

無理やりやるとして自分ならテーブル値関数でこういうのを作って

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[utf_Split]
(
    @String nvarchar(max),
    @Delimiter nvarchar(10)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos,times) 
    AS(
        SELECT 0 AS stpos,CHARINDEX(@Delimiter,@String) AS endpos,1 as times
        UNION ALL
        SELECT cast(endpos+1 as int),CHARINDEX(@Delimiter,@String,endpos+1),times+1
        FROM Split
        WHERE endpos > 0
    )
    SELECT times as Id
		,SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos) as Data
		,stpos,endpos,max(times) over() as times
    FROM Split
    where endpos is not null
)

行数をIDとして一致させて実行します。

select * from [dbo].[utf_Split]('1,2,3',',')
select * from [dbo].[utf_Split]('10,20,30',',')

select A.Data as 商品コード
	,B.Data as 個数
from (
	select * from [dbo].[utf_Split]('1,2,3',',')
) as A
	inner join (
		select * from [dbo].[utf_Split]('10,20,30',',')
	) as B on B.Id=A.Id

-- もちろんこれでもOKです
select A.Data as 商品コード
	,B.Data as 個数
from [dbo].[utf_Split]('1,2,3',',') as A
    inner join [dbo].[utf_Split]('10,20,30',',') as B on B.Id=A.Id

↓実行結果

Comments

[bose]

ご回答ありがとうございます！
また、色々なことをご教授ありがとうございます！