我得到的结果与表1相似,但我需要的价值如下面我提到的表2 [英] I Getting Result As Like Table1 But I Need Value Like Table 2 Which I Mentioned Below
本文介绍了我得到的结果与表1相似,但我需要的价值如下面我提到的表2的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用sql表
tabl1
======================= ==
name 1 2 3 4 5
============== ===========
sekar p
ramu p
sekar p
ramu p
sekar a
ramu p
sekar p
ramu a
==========================
i想要这个< span class =code-keyword> type table ..
table2
=========================
name 1 2 3 4 5
=========================
sekar ppap
ramu pppa
=========================
解决方案
尝试:
SELECT 名称, MAX([ 1 ]),MAX([ 2 ]),MAX([ 3 ]),MAX([ 4 ]),MAX([ 5 ]) FROM 表1
GROUP BY 名称
ORDER BY 名称 DESC
如果以下内容将创建并填充表create table table1
(
name varchar ( 255 ),
[ 1 ] char ,
[ 2 ] char ,
[ 3 ] char ,
[ 4 ] char ,
[ 5 ] char
)
insert into table1 values
(' sekar',' p', null , null , null , null ),
(' ramu',' p', null , null , null , null ),
(' sekar', null ,' p', null , null , null ),
(' ramu', null ,' p', null , null , null ),
(< span class =code-string>' sekar', null , null ,' a', null , null ),
(' ramu', null , null ,' p',< span class =code-keyword> null , null ),
(' sekar', null , null , null ,' p',< span class =code-keyword> null ),
(' ramu', null , null , null ,' a', null )然后此查询将生成您想要的结果
选择名称,max(isnull([ 1 ],' ')) as [ 1 ],max(isnull([ 2 ],' ')) as [ 2 ],
max(isnull([ 3 ],' '))< span class =code-keyword> as [ 3 ],max([ 4 ]) as [ 4 ],max(isnull([ 5 ],' ')) as [ 5 ]
来自 table1
group by name
order by name desc但是,如果你的第一个表是这样的结果< pre lang =sql> 创建 table tabl1
(
name varchar ( 255 ),
num int ,
achar char ( 1 )
)
插入 进入 tabl1 值
(' sekar', 1 ,' p'),
(' ramu', 1 ,' p'),
(' sekar', 2 ,' p'),
(' ramu', 2 , ' p'),
(' sekar', 3 ,' a'),
(' ramu ', 3 ,' p'),
(' sekar', 4 ,' p'),
(' ramu', 4 ,' a')那么这就是你所需要的
SELECT name ,isnull([ 1 ],' ') as [ 1 ],isnull([ 2 ],' ') as [ 2 ],
isnull([ 3 ],' ') as [ 3 ],isnull([ 4 ],' ') as [ 4 ],isnull([ 5 ],' ') as [ 5 ]
FROM (
SELECT
name,num,achar
FROM tabl1
< span class =code-keyword>其中 achar in (' a',' p')
) as s
PIVOT
(
max(achar)
FOR num IN ([ 1 ],[ 2 ],[ 3 ],[ 4 ],[ 5 ])
) AS p
订单 按名称 desc
using sql table
tabl1
=========================
name 1 2 3 4 5
=========================
sekar p
ramu p
sekar p
ramu p
sekar a
ramu p
sekar p
ramu a
==========================
i want this type of table..
table2
=========================
name 1 2 3 4 5
=========================
sekar p p a p
ramu p p p a
=========================
解决方案
Try:
SELECT Name, MAX([1]), MAX([2]), MAX([3]), MAX([4]), MAX([5]) FROM Table1 GROUP BY Name ORDER BY Name DESC
If the following will create and populate your tablecreate table table1 ( name varchar(255), [1] char, [2] char, [3] char, [4] char, [5] char ) insert into table1 values ('sekar','p',null, null, null, null), ('ramu', 'p',null, null, null, null), ('sekar',null, 'p', null, null, null), ('ramu',null, 'p', null, null, null), ('sekar',null, null, 'a', null, null), ('ramu',null, null, 'p', null, null), ('sekar',null,null, null, 'p', null), ('ramu',null, null, null,'a', null)then this query will produce the results you want
select name, max(isnull([1],'')) as [1],max(isnull([2],'')) as [2], max(isnull([3],'')) as [3], max([4]) as [4], max(isnull([5],'')) as [5] from table1 group by name order by name descHowever, if your first table is the result of something like this
create table tabl1 ( name varchar(255), num int, achar char(1) ) insert into tabl1 values ('sekar',1,'p'), ('ramu',1,'p'), ('sekar',2,'p'), ('ramu',2,'p'), ('sekar',3,'a'), ('ramu',3,'p'), ('sekar',4,'p'), ('ramu',4,'a')then this is what you need
SELECT name, isnull([1],'') as [1],isnull([2],'') as [2], isnull([3],'') as [3],isnull([4],'') as [4],isnull([5],'') as [5] FROM ( SELECT name, num, achar FROM tabl1 where achar in ('a','p') ) as s PIVOT ( max(achar) FOR num IN ([1],[2],[3],[4],[5]) )AS p order by name desc
这篇关于我得到的结果与表1相似,但我需要的价值如下面我提到的表2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文