This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / 求SQL Server query!I need a query that can generate the following output:
Item Value
XXXXX aaaaaaa
bbbbbbbbb
ccc
YYYY mmmmm
nnnnnnnn
ZZZZZZZ EEEEE
FFFFFFFFF
GGGGGGGGGGG
HHH
-xxu99(春眠不觉晓);
2012-7-18
{241}
(#7663930@0)
-
select 'XXXXX' Item, 'aaaaaaa' Value
union select 'bbbbbbbbb', null
union select 'ccc', null
...
-c1xwy(洪兴罩俺去战斗);
2012-7-18
(#7663984@0)
-
Thanks but I want to appear once in each Item group with multiple values.
-xxu99(春眠不觉晓);
2012-7-18
(#7664206@0)
-
没看懂你得格式，用模拟数据是。。。
-zbeifly(不是爱风尘似被前缘误);
2012-7-18
(#7664217@0)
-
The format is like this:In column1, it has 2008, 2009, ..., 2011. Each year only appears once, other cells are blank.
In column2, it has Jan, Feb, ....Dec.
So the row1 is 2008, Jan; row2 is null, Feb; row3 is null, Mar; row12 is null, Dec; row13 is 2009, Jan; row14 is null, Feb, ...;
row48 is null, Dec.
The column3 will be other data. DISTINCT won't help here.
-xxu99(春眠不觉晓);
2012-7-18
{345}
(#7664746@0)
-
这个select case when SeqM = 1 then y end y, m
from
(
select *, ROW_NUMBER() over( order by y) SeqY
from
(
select '2010' y
union select '2011'
union select '2012'
) y
) y,
(
select *, ROW_NUMBER() over( order by m) SeqM
from
(
select 'aaa' m
union select 'bbb'
union select 'ccc'
union select 'ddd'
) m
) m
order by SeqY, SeqM
-c1xwy(洪兴罩俺去战斗);
2012-7-18
{400}
(#7664795@0)
-
rank() 应该是你要的select case when rank() over (partition by y order by y, m) = 1 then y end y, m,
rank() over (partition by y order by y, m) rnk
from
(
select '2010' y, 'aaa' m
union select '2010', 'bbb'
union select '2012', 'ccc'
union select '2012', 'fff'
union select '2013', 'ddd'
union select '2013', 'eee'
) t
-c1xwy(洪兴罩俺去战斗);
2012-7-18
{340}
(#7664822@0)
-
A subquery makes thing easy.Select CASE When row =1 then Year else null End year, month from
(Select ROW_NUMBER() over (PARTITION BY year order by month) as row, year, month from data_source) A
-deep_blue(BLUE);
2012-7-18
{167}
(#7665255@0)
-
not sure if I understand your question correctly. If you want to ignore the duplicate value for one column, you should use SQL SELECT DISTINCT Syntax
-littlebird09(小小鸟);
2012-7-18
(#7664242@0)