当前位置：首页 » 编程语言 » sqlserver列转多行
扩展阅读
提分平台的账号密码是什么 2023-08-31 22:07:10
webinf下怎么引入js 2023-08-31 21:54:13
堡垒机怎么打开web 2023-08-31 21:54:11

sqlserver列转多行

发布时间: 2022-05-04 04:56:33

‘壹’ mssql2008将一列值转换多行，不要用while循环，

CREATEFunctionSplit
(
@pvarchar(8000),
@dvarchar(10)
)
returns@tTable(tokenvarchar(100))
As
Begin
Declare@iInt
Set@p=RTrim(LTrim(@p))
Set@i=CharIndex(@d,@p)

While@i>=1
Begin
Insert@tValues(Left(@p,@i-1))
Set@p=SubString(@p,@i+1,Len(@p)-@i)
Set@i=CharIndex(@d,@p)
End

If@p<>''
Insert@tValues(@p)

Return
End


witht(id,name)as(select1,'a1:a2:a3:a4'
unionallselect2,'b1:b2:b3')
select*fromtcrossapplysplit(t.name,':')

‘贰’ sqlserver怎么行转列

1，首先创建数据表
2，接着插入演示数据
3，需要把学生的每门成绩集中到一行
4，编写PIVOT函数实现
5，运行以后实现行转列
6，最后还可以用case when来实现

‘叁’ mssql根据条件数据列分解成多行数据，急

创建测试表

createtablet
(idint,
namevarchar(10),
typevarchar(10))

insertintotvalues(1,'苹果','003')
insertintotvalues(2,'香蕉','A04')
insertintotvalues(3,'西瓜',NULL)
insertintotvalues(4,'石榴','002')

运行

selectt.*,t.name+s.name2name2fromtleftjoin
(selectdistincttype,
casewhenisnumeric(replace(type,0,''))=0then
left(replace(type,0,''),1)+cast(t1.idasvarchar)+'级'elsecast(t1.idasvarchar)+'级'endname2
fromt,
(select1inionall
select2inionall
select3inionall
select4id)t1
wheretypeisnotnull
andt1.id<=right(replace(type,0,''),1))s
ont.type=s.type

结果

selectdistincttype,
casewhenisnumeric(replace(type,0,''))=0then
left(replace(type,0,''),1)+cast(t1.idasvarchar)+'级'elsecast(t1.idasvarchar)+'级'endname2
fromt,
(select1inionall
select2inionall
select3inionall
select4id)t1
wheretypeisnotnull
andt1.id<=right(replace(type,0,''),1)

‘肆’ sqlserver如何列转行

CREATETABLE#test(
snINT,
enINT
);

INSERTINTO#testVALUES(1,5);
INSERTINTO#testVALUES(6,7);
INSERTINTO#testVALUES(8,8);
INSERTINTO#testVALUES(9,12);
go


SELECTDISTINCT
m.number
FROM
#testt
JOINmaster..spt_valuesm
	ON(
	t.sn<=m.number
		ANDt.en>=m.number
	);
go


查询结果:

number
-----------
1
2
3
4
5
6
7
8
9
10
11
12

(12行受影响)

‘伍’ sqlserver2005 纵向的一列变成一行输出（一行一列）。

看效果是不是这样:

测试案例:

SELECT*fromTab
--sql语句如下:
select[type],STUFF(
(SELECT','+namefromTabt2WHEREt1.type=t2.typeFORXMLPATH(''))--通过Type过滤转换成文本同时以(逗号、)分隔
,1,1,'')infofromtabt1GROUPby[type]orderbyt1.[type],info//分组后排序

效果图如下:

--关键这段代码:你拿去数据库中执行一下将表的行式转成XML格式字符串当遇到标签与字符相加时会去掉标签!
SELECT','+namefromTabt2FORXMLPATH('')

希望能帮到你！

‘陆’ sqlserver 行列转换问题

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(1),[col2] varchar(6))
insert [tb]
select 'A','a' union all
select 'B','b,c,d' union all
select 'C','c,aa,e' union all
select 'E','e'
go

select
a.col1,
col2=substring(a.col2,b.number,charindex(',',col2+',',b.number)-b.number)
from
tb a,master..spt_values b
where
b.type='P'
and
substring(','+a.col2,b.number,1)=','

/**
col1 col2
---- ------
A a
B b
B c
B d
C c
C aa
C e
E e

(8 行受影响)
**/

‘柒’ 如何把sqlserver中的列转换成行，行转换成列，显示

create database arron
go
use arron
go

-- createTable init Data
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('张三','语文',20)
insert into students values ('张三','数学',90)
insert into students values ('张三','英语',50)
insert into students values ('李四','语文',81)
insert into students values ('李四','数学',60)
insert into students values ('李四','英语',90)

-- solution1
select * from students
pivot(
max(grade)
FOR [class] IN ([语文],[数学],[英语])
) AS pvt

-- solution2 相当于自连接

select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语
from students A,students B,students C
where A.Name=B.Name and B.Name=C.Name
and A.class='语文' and B.class='数学'
and C.class='英语'

-- solution3
select name,
max(case when s.class='语文' then s.grade end) as 语文,
max(case when s.class='数学' then s.grade end) as 数学,
max(case when s.class='英语' then s.grade end) as 英语
from students s group by name

--在有id 的情况下
create table students2 (
id int primary key identity(1,1),
name varchar(25),
class varchar(25),
grade int
)

insert into students2 values ('张三','语文',20)
insert into students2 values ('张三','数学',90)
insert into students2 values ('张三','英语',50)
insert into students2 values ('李四','语文',81)
insert into students2 values ('李四','数学',60)
insert into students2 values ('李四','英语',90)

-- 原先的solution1(有问题)
select * from students2
pivot(
max(grade)
FOR [class] IN ([语文],[数学],[英语])
) AS pvt
-- 原先的solution2 (ok)
select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语
from students A,students2 B,students2 C
where A.Name=B.Name and B.Name=C.Name
and A.class='语文' and B.class='数学'
and C.class='英语'
-- 原先的solution3 （ok）
select name,
max(case when s.class='语文' then s.grade end) as 语文,
max(case when s.class='数学' then s.grade end) as 数学,
max(case when s.class='英语' then s.grade end) as 英语
from students s group by name

--unpivot 函数使用
create table test1(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test1 values(1,'a',1000,2000,4000,5000)
insert into test1 values(2,'b',3000,3500,4200,5500)

--实现的sql
select * from test1

select id ,[name],[ji],[xiaoshou] from test1
unpivot
(
xiaoshou for ji in
([q1],[q2],[q3],[q4])
)
as f

--- 以下的sql 可以替换上面的sql
select id,[name],
ji='Q1',
xiaoshou=(select Q1 from test1 where id=a.id)
from test1 as a
union
select id,[name],
ji='Q2',
xiaoshou=(select Q2 from test1 where id=a.id)
from test1 as a
union
select id,[name],
ji='Q3',
xiaoshou=(select Q3 from test1 where id=a.id)
from test1 as a
union
select id,[name],
ji='Q4',
xiaoshou=(select Q4 from test1 where id=a.id)
from test1 as a

‘捌’ sqlserver列转行 查询

select*fromA_14072201

IDUSERID
4685938130
46859888
4681138130
4681137761
4647037836
4647037836
4643037836
4643037836
selectID,USERIDfromA_14072201
whereIDin(selectIDfrom(selectID,USERIDfromA_14072201groupbyID,USERID)astableAgroupbyIDhavingCOUNT(*)=2)

IDUSERID
4685938130
46859888
4681138130
4681137761
selecttable1.ID,table1.USERID,table2.USERID
from(selectID,USERIDfrom(selectID,USERID,ROW_NUMBER()over(orderbyID)ROWNUMBERfromA_14072201whereIDin(selectIDfrom(selectID,USERIDfromA_14072201groupbyID,USERID)astableAgroupbyIDhavingCOUNT(*)=2))astableBwhereROWNUMBER%2=1)table1
innerjoin
(selectID,USERIDfromA_14072201whereIDin(selectIDfrom(selectID,USERIDfromA_14072201groupbyID,USERID)astableAgroupbyIDhavingCOUNT(*)=2))table2
ontable1.ID=table2.IDandtable1.USERID<>table2.USERID

IDUSERIDUSERID
468113813037761
4685938130888
--结果是对的，写的有点乱，你捋一捋然后优化一下，优化不了就直接改成表值函数去处理，我工作去了，话说你这个问题是半夜2：39发的，要注意身体啊。

‘玖’ MS SQL SERVER 如何把多列的值 , 变成一列多行 .

  1. 行转列，列转行是我们在开发过程中经常碰到的问题

  2. 行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。

  3. 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT...CASE 语句中所指定的语法更简单、更具可读性.

  4. --行转列的静态方案一：CASE WHEN,兼容sql2000
    select custid,
    3 sum(case when YEAR(orderdate)=2002 then qty end) as [2002],
    sum(case when YEAR(orderdate)=2003 then qty end) as [2003],
    sum(case when YEAR(orderdate)=2004 then qty end) as [2004]
    from orders
    group by custid;

  5. --行转列的静态方案二：PIVOT,sql2005及以后版本
    select *
    from (select custid,YEAR(orderdate) as years,qty from orders) as ord
    pivot(sum(qty) for years in([2002],[2003],[2004]))as p

阅读全文

与sqlserver列转多行相关的内容

本站内容整理源于互联网，如有问题请联系解决。
Copyright design: www.gotrillian.com since 2022