IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test CREATE TABLE #test ( Mobile VARCHAR(11) ,States INT )IF OBJECT_ID('tempdb..#test1','U') IS NOT NULL DROP TABLE #test1 CREATE TABLE #test1 ( ID INT ,Number VARCHAR(11) ,NumberName NVARCHAR(200) ,Price DECIMAL(9,2) )INSERT INTO #test SELECT '13100001231', 1 UNION ALL SELECT '13100005201', 1 UNION ALL SELECT '13166635210', 1 UNION ALL SELECT '13119500101', 1 UNION ALL SELECT '13100956328', 1INSERT INTO #test1 SELECT 1, '520', '谐音', 10 UNION ALL SELECT 2, '0000', '中4零', 20 UNION ALL SELECT 3, '1950', '日期', 30 UNION ALL SELECT 4, '123', '顺序', 40 UNION ALL SELECT 5, '666', '连续', 50 SELECT A.Mobile ,ISNULL(T.NumberName,'') AS NumberName ,ISNULL(T.Price,0) AS Price INTO #test3 FROM #test AS A OUTER APPLY (SELECT NumberName,Price FROM #test1 AS B WHERE CHARINDEX(b.Number,A.Mobile,0)>0) T DECLARE @total INT,@Sql NVARCHAR(MAX)SELECT @total= MAX(cnt) FROM (SELECT COUNT(1) AS cnt FROM #test3 GROUP BY Mobile)AS A SELECT @Sql=ISNULL(@Sql+',','') +'MAX(CASE WHEN rn='+RTRIM(a.number)+' THEN NumberName ELSE '''' END) AS NumberName,' +'MAX(CASE WHEN rn='+RTRIM(a.number)+' THEN Price ELSE 0 END) AS Price' FROM master.dbo.spt_values AS A WHERE A.type='P' AND number BETWEEN 1 AND @total
SELECT @Sql='SELECT T.Mobile ,'+@Sql+' FROM ( SELECT *,ROW_NUMBER() OVER(PARTITION BY Mobile ORDER BY GETDATE()) AS rn FROM #test3 )AS T GROUP BY T.Mobile'
EXECUTE(@Sql)
select E.Mobile,E.NumberName,E.Price into table3 from (select T.Mobile,D.NumberName,D.Price from table1 T left join (SELECT A.mobile,NumberName,Price FROM table1 A ,table2 B WHERE CHARINDEX(b.Number,A.Mobile,0)>0) D on T.mobile=D.Mobile )E declare @sql nvarchar(max); set @sql=''; ;with t as ( select *,ROW_NUMBER() over(Partition by mobile order by @@servername) rownum from table3 )
select @sql = @sql +',max(case when rownum ='+cast(rownum as varchar)+' then NumberName else null end) as NumberName'+ ',max(case when rownum = '+cast(rownum as varchar)+' then Price else null end) as Price' from t group by rownumSELECT @Sql='SELECT Mobile' + @Sql + ' from (select *,ROW_NUMBER() over(Partition by mobile order by @@servername) rownum from table3 )t' + ' GROUP BY Mobile '
CREATE TABLE #test
(
Mobile VARCHAR(11)
,States INT
)IF OBJECT_ID('tempdb..#test1','U') IS NOT NULL DROP TABLE #test1
CREATE TABLE #test1
(
ID INT
,Number VARCHAR(11)
,NumberName NVARCHAR(200)
,Price DECIMAL(9,2)
)INSERT INTO #test
SELECT '13100001231', 1 UNION ALL
SELECT '13100005201', 1 UNION ALL
SELECT '13166635210', 1 UNION ALL
SELECT '13119500101', 1 UNION ALL
SELECT '13100956328', 1INSERT INTO #test1
SELECT 1, '520', '谐音', 10 UNION ALL
SELECT 2, '0000', '中4零', 20 UNION ALL
SELECT 3, '1950', '日期', 30 UNION ALL
SELECT 4, '123', '顺序', 40 UNION ALL
SELECT 5, '666', '连续', 50
SELECT A.Mobile
,ISNULL(T.NumberName,'') AS NumberName
,ISNULL(T.Price,0) AS Price
INTO #test3
FROM #test AS A
OUTER APPLY (SELECT NumberName,Price FROM #test1 AS B WHERE CHARINDEX(b.Number,A.Mobile,0)>0) T
DECLARE @total INT,@Sql NVARCHAR(MAX)SELECT @total= MAX(cnt) FROM (SELECT COUNT(1) AS cnt FROM #test3 GROUP BY Mobile)AS A
SELECT @Sql=ISNULL(@Sql+',','')
+'MAX(CASE WHEN rn='+RTRIM(a.number)+' THEN NumberName ELSE '''' END) AS NumberName,'
+'MAX(CASE WHEN rn='+RTRIM(a.number)+' THEN Price ELSE 0 END) AS Price'
FROM master.dbo.spt_values AS A
WHERE A.type='P'
AND number BETWEEN 1 AND @total
SELECT @Sql='SELECT T.Mobile ,'+@Sql+'
FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY Mobile ORDER BY GETDATE()) AS rn
FROM #test3 )AS T
GROUP BY T.Mobile'
EXECUTE(@Sql)
from (select T.Mobile,D.NumberName,D.Price from table1 T left join
(SELECT A.mobile,NumberName,Price FROM table1 A ,table2 B
WHERE CHARINDEX(b.Number,A.Mobile,0)>0) D on T.mobile=D.Mobile )E declare @sql nvarchar(max);
set @sql='';
;with t
as
(
select *,ROW_NUMBER() over(Partition by mobile order by @@servername) rownum
from table3
)
select @sql = @sql +',max(case when rownum ='+cast(rownum as varchar)+' then NumberName else null end) as NumberName'+
',max(case when rownum = '+cast(rownum as varchar)+' then Price else null end) as Price'
from t group by rownumSELECT @Sql='SELECT Mobile' + @Sql +
' from (select *,ROW_NUMBER() over(Partition by mobile order by @@servername) rownum
from table3
)t' +
' GROUP BY Mobile '
exec(@sql)