请查看下表 [英] Please find the tables below

问题描述

我有这样的桌子

ID姓名

1 Naveen

2 Naveen

3 Naveen

4 Praveen

5 Praveen





我需要下面提到的结果，如果需要的话你可以用col1，col2和Col3创建新列，或者你只使用case语句来使用select语句。



ID名称<> Col1<> Col2< ;> Col3

1 Naveen<> 1<> null<> null

2 Naveen<> null<> 2<> null

3 Naveen<> null<> null<> 3

4 Praveen<> 1<> null<> null

5 Praveen<> null<> 2<> null



编辑 - OP发布的解决方案内容

<前lang =text> ID名称Col1 Col2 Col3
1 Naveen 1 null null
2 Naveen null 2 null
3 Naveen null null 3
4 Praveen 1 null null
5 Praveen null 2 null

是我需要的确切输出....请分享上面的查询ssolution

解决方案

使用非常小的样本集和缺乏规则，这将得到你想要的东西。

 选择 [id]，名称，
 案例 当 [id]％ 3  =  1  然后  1   end   as  col1，
  case  何时 [id] ％ 3  =  2  然后  2   end   as  col2，
 案例 何时 [id]％  3  =  0  然后  3   end   as  col3 
 来自名称

如果您需要更多Col n 列，或者每个名字都有三个以上，那么它就行不通了。



如果你;试图说ID是 n Name的实例那么您可能需要将信息解析为临时表并查看旋转结果以获得col n 。

I have table like this
ID Name
1 Naveen
2 Naveen
3 Naveen
4 Praveen
5 Praveen


I need result mentioned below, If required u can create new columns as col1,col2 and Col3 or you u use select statement only by using case statement.

ID Name <>Col1<>Col2<>Col3
1 Naveen <>1<>null<>null
2 Naveen <>null<>2<>null
3 Naveen <>null<>null<>3
4 Praveen <>1<>null<>null
5 Praveen <>null<>2<>null

Edit - content of solution posted by OP

ID Name      Col1     Col2	    Col3
1  Naveen    1	      null	         null
2  Naveen    null     2             null
3  Naveen    null     null          3
4  Praveen   1        null          null
5  Praveen   null     2             null

Yes exact output i required.... Please share query of that above ssolution

解决方案

With your very small sample set and lack of rules, this will get you what you want.

select [id], name,
        case when [id] % 3 = 1 then 1 end as col1,
        case when [id] % 3 = 2 then 2 end as col2,
        case when [id] % 3 = 0 then 3 end as col3
from names

If you need more Coln columns, or there are more than three of each name, then it won't work.

If you;re trying to say that "ID is the nth instance of Name" then you might need to parse the info into a temp table and look at pivoting the results to get the coln.

这篇关于请查看下表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！