This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

try this..

sunday8(每个年头都有新的梦景)

with t as(
select 'mross' UID, '2345' Itemid union
select 'mross' UID, '2897' Itemid union
select 'mross' UID, '2678' Itemid union
select 'jford' UID, '3958' Itemid union
select 'jford' UID, '6898' Itemid union
select 'jford' UID, '7582' Itemid union
select 'jford' UID, '2278' Itemid union
select 'jford' UID, '3768' Itemid
),

t2 (Uid, Items) as
(
SELECT UID, STUFF( ( SELECT ','+ Itemid
FROM t a
WHERE b.UID = a.Uid order by itemid asc
FOR XML PATH('')),1 ,1, '') ItemId
FROM t b
GROUP BY Uid
)
select * from t2 inner join t3 on t2.Uid=t3.uid and t2.Items <>t3.items

(#7380168@0)
Last Updated: 2012-3-23
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 学科技术讨论 / I need compare data in two tables. However, two tables have different structures. Any one has any idea? -deep_blue(BLUE); 2012-3-23 {259} (#7378621@0)
In table one, data are as follows
UID ItemID
mross 2345
mross 2897
mross 2678
jford 3958
jford 6898
jford 7582
jford 2278
jford 3768
… …

In table two, data are as follows
UID Items
mross 2345, 2678, 2897
jford 2278, 3958, 6898, 7582
… …

Write a macro -c__wang(自私的寄生虫); 2012-3-23 (#7378637@0)
Access, sql -abesun(动平衡); 2012-3-23 (#7378652@0)
写个VB，switch row to column就行了 -fly_universe(飞越时空); 2012-3-23 (#7378660@0)

In table two, there is only one spot for all items separated by comma. -deep_blue(BLUE); 2012-3-23 (#7379050@0)

你可以把这一cell的内容copy到notepad or word file,replace all commas to spaces, then import to excell again -fly_universe(飞越时空); 2012-3-23 (#7379165@0)

You must be kidding. How can you copy million cells? -deep_blue(BLUE); 2012-3-23 (#7379682@0)

You didn't say there were million cells. You said only one spot... -fly_universe(飞越时空); 2012-3-23 (#7379712@0)

You never deal with database. -deep_blue(BLUE); 2012-3-23 (#7379744@0)

To be honest, your question does not seem to asked by a person who is handling database on a daily basis. -fly_universe(飞越时空); 2012-3-23 (#7379758@0)

copy, then paste special, click transpose. -uptowngirl(若初朝花夕拾); 2012-3-23 (#7378667@0)
Unix way: awk. -holdon(again); 2012-3-23 (#7378748@0)
I need find out missing items in both tables for each user. I think it should have some way to figure out by SQL. -deep_blue(BLUE); 2012-3-23 (#7378974@0)

You just need to seperate the comma list in table 2 then compare with table 1. In Sql server, you can use CTE and XML to get what you want. Here is an example for your reference. -flowercity(碎花之城); 2012-3-23 (#7379112@0)

仰视 -secondlasttry(镀锌铁瓦); 2012-3-23 (#7379221@0)
Since data are put into Test table, why still need CTE? -deep_blue(BLUE); 2012-3-23 (#7379287@0)

The test table is still comma list data same as your table 2, you either create a temp table to replace comma list with xml format or use CTE instead to convert table 2 to the same structure as table 1, then do except to find the difference. -flowercity(碎花之城); 2012-3-23 (#7379466@0)

Got it. Thx. -deep_blue(BLUE); 2012-3-23 (#7379550@0)

Never use SQL's XML type. It's real fantastic. -deep_blue(BLUE); 2012-3-23 (#7379446@0)

If the structures of the tables are very much alike, I sometimes use UltraEdit to make comparisons. -c__wang(被比较处劣势很平静); 2012-3-23 (#7379486@0)

UltraEdit is pretty convenient -fly_universe(飞越时空); 2012-3-23 (#7379770@0)

try this.. -sunday8(每个年头都有新的梦景); 2012-3-23 {704} (#7380168@0)
with t as(
select 'mross' UID, '2345' Itemid union
select 'mross' UID, '2897' Itemid union
select 'mross' UID, '2678' Itemid union
select 'jford' UID, '3958' Itemid union
select 'jford' UID, '6898' Itemid union
select 'jford' UID, '7582' Itemid union
select 'jford' UID, '2278' Itemid union
select 'jford' UID, '3768' Itemid
),

t2 (Uid, Items) as
(
SELECT UID, STUFF( ( SELECT ','+ Itemid
FROM t a
WHERE b.UID = a.Uid order by itemid asc
FOR XML PATH('')),1 ,1, '') ItemId
FROM t b
GROUP BY Uid
)
select * from t2 inner join t3 on t2.Uid=t3.uid and t2.Items <>t3.items

Inner join is for common portion of two set. Obviously, missing items are out of the common portion. -deep_blue(BLUE); 2012-3-26 {164} (#7385643@0)
As a matter fact, outer join is for this kind of task. Since it requires to find out missing items in both sides (Left and right), a full outer join should be used.

@Calgary

try this..

Replies, comments and Discussions:

More Topics