表a:
id bindid name
1 1 wang
2 2 zhang
表b:
id bindid address
1 1 aaa
2 1 bbb
3 2 ccc
4 2 ddd
想实现的效果:
bindid name address1 address2
1 wang aaa bbb
2 zhang ccc ddd
请大神帮忙解答 由于平台限制不能使用存储过程,请问有什么其他的方法可以实现
id bindid name
1 1 wang
2 2 zhang
表b:
id bindid address
1 1 aaa
2 1 bbb
3 2 ccc
4 2 ddd
想实现的效果:
bindid name address1 address2
1 wang aaa bbb
2 zhang ccc ddd
请大神帮忙解答 由于平台限制不能使用存储过程,请问有什么其他的方法可以实现
解决方案 »
- 怎样让copy path1 path2命令中的path1可以动态生成?
- 在SQL中统计一个字段 然后再插入到另一张表的一个字段
- sql server经常被黑,有什么办法可以看到历史sql语句执行情况?
- 关于一个批量插入的问题
- 求sql语句,大侠赐教
- 哪位能给讲讲job owner的权限和sql agent start account权限在job调用和job执行的各个阶段起什么作用?
- sqlserver2005中附加数据库失败
- 关于日期比较的问题很菜的
- 请教 先后调用两次存储过程为什么比只调用一次要快得多??
- SQL SERVER 2005 中,用存储过程分页,涉及到要通过外表查询获得的Count值进行排序
- 连接远程SQL SERVER Server=xxx.xxx.xxx.xxx;Address=xxx.xxx.xxx.xxx 是什么意思
- 中秋放分sql生成行号
FROM (
SELECT b.bindid,b.bname,addr=stuff((SELECT ','+ addr FROM cte a WHERE a.bindid=b.bindid FOR XML path('')),1,1,'')
FROM cte b
GROUP BY bindid,bname) c
SELECT * INTO #T FROM (
SELECT 1 id,1 bindid, 'wang' name
UNION SELECT 2,2,'zhang'
)ZSELECT * INTO #T1 FROM (
SELECT 1 id,1 bindid, 'aaa' [address]
UNION SELECT 2,1,'bbb'
UNION SELECT 3,2,'ccc'
UNION SELECT 4,2,'ddd'
)Z SELECT A.bindid,A.name,B.[address] INTO #T2 FROM #T A
LEFT JOIN #T1 B ON B.bindid=A.bindidSELECT *,ROW_NUMBER() OVER(PARTITION BY name ORDER BY bindid,name,[address]) ID INTO #T3 FROM #T2DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT bindid,name'SELECT @SQL=@SQL+',MAX(CASE WHEN ID='+CONVERT(VARCHAR,ID)+' THEN [address] ELSE NULL END) [address'+CONVERT(VARCHAR,ID)+']'
FROM #T3
GROUP BY ID
ORDER BY ID
SET @SQL=@SQL+'FROM #T3 GROUP BY bindid,name'
EXEC(@SQL)
DROP TABLE #T
DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3
bindid name address1 address2
----------- ----- -------- --------
1 wang aaa bbb
2 zhang ccc ddd
CREATE TABLE #tmp_1 (id INT , bindid INT , NAME VARCHAR(10) )
CREATE TABLE #tmp_2 (id INT , bindid INT , addr VARCHAR(10) )INSERT INTO #tmp_1( id, bindid, NAME ) VALUES ( 1, 1, 'wang' )
INSERT INTO #tmp_1( id, bindid, NAME ) VALUES ( 2, 2, 'zhang')
INSERT INTO #tmp_2( id, bindid, addr ) VALUES ( 1,1,'aaa')
INSERT INTO #tmp_2( id, bindid, addr ) VALUES ( 2,1,'bbb')
INSERT INTO #tmp_2 ( id, bindid, addr ) VALUES ( 3,2,'ccc' )
INSERT INTO #tmp_2( id, bindid, addr )VALUES ( 4,2,'ddd')
;
WITH cte AS (
SELECT ROW_NUMBER() OVER(PARTITION BY b.bindid ORDER BY b.id) cnt,a.bindid ,a.name,b.addr
FROM #tmp_2 b JOIN #tmp_1 a ON a.bindid=b.bindid
)
SELECT c.bindid,c.NAME,c.addr1,d.addr2 FROM
(SELECT bindid ,NAME,addr addr1,cnt FROM cte) c
JOIN
(
SELECT bindid ,NAME,addr addr2 FROM cte) d
ON c.bindid=d.bindid AND c.NAME=d.NAME AND c.addr1<>d.addr2
WHERE c.cnt=1bindid NAME addr1 addr2
----------- ---------- ---------- ----------
1 wang aaa bbb
2 zhang ccc ddd
drop table #t;
if OBJECT_ID('tempdb..#t1') > 0
drop table #t1;
SELECT * INTO #T FROM (
SELECT 1 id,1 bindid, 'wang' name
UNION SELECT 2,2,'zhang'
UNION SELECT 3,3,'jiang'
)Z
SELECT * INTO #T1 FROM (
SELECT 1 id,1 bindid, 'aaa' [address]
UNION SELECT 2,1,'bbb'
UNION SELECT 3,2,'ccc'
UNION SELECT 4,2,'ddd'
UNION SELECT 5,3,'jjj'
UNION SELECT 6,3,'zzz'
UNION SELECT 7,3,'hhh'
)Zdeclare @str varchar(max) = '';with cte as
(
select ROW_NUMBER()over(partition by bindid order by (select 1)) nid
from #t1
group by id,bindid
)
select @str=@str + QUOTENAME('address' + CAST(number as varchar(10))) + ','
from master..spt_values a
where type = 'p' and number > 0 and number <=(select MAX(nid) from cte)
select @str = LEFT(@str,len(@str) - 1);select @str=
'with cte as
(
select' +'''address''' + '+ cast(ROW_NUMBER()over(PARTITION by name order by name) as varchar(20)) id,
a.bindid,address,name
from #t1 a
join #t b on b.bindid = A.bindid
)
select * from cte
pivot
(min(address) for id in('+@str+'))b'EXEC (@STR)
from (select bindid,address,row_number() over(PARTITION by
bindid order by id) as n from #T1
) as t pivot
( max(address) for n in ([1],[2])) as p join #T
on #T.id=p.bindid