22,210
社区成员
发帖
与我相关
我的任务
分享SQL如何把第一列变标题
(无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名)
科室\大类 西药费 中成药费 中草药费 卫生材料费 中药饮片 院内制剂 无 无
放射科 117.42 0.00 0.00 0.00 0.00 0.00 0.00 0.00
风湿科 125354.50 56513.34 154381.42 891.61 152991.38 10578.90 0.00 0.00这里第一行是标题，意味着原来没有标题，我想把查询内容的第一行变成标题，这个可以实现吗？
顺便求简单的行转列
top_no top_name
01 西药费
02 中成药费
03 中草药费
04 卫生材料费
05 中药饮片
06 院内制剂
西药费 中成药费 中草药费 卫生材料费 中药饮片 院内制剂
01 02 03 04 05 06
...全文
请发表友善的回复…
发表回复
二月十六 2017-06-17
- 打赏
- 举报
还有好多好多
不穿内裤的超人 2017-06-17
- 打赏
- 举报
可以，类似这样
[/quote]还有你不会的东西吗？
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a] nvarchar(25),[b] nvarchar(29),[c] nvarchar(28),[d] nvarchar(29),[e] nvarchar(26),[f] nvarchar(29),[g] nvarchar(28),[h] nvarchar(24),[i] nvarchar(24))
Insert #T
select N'科室\大类',N'西药费',N'中成药费',N'中草药费',N'卫生材料费',N'中药饮片',N'院内制剂',N'无',N'无' union all
select N'放射科',N'117.42',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00' union all
select N'风湿科',N'125354.50',N'56513.34',N'154381.42',N'891.61',N'152991.38',N'10578.90',N'0.00',N'0.00'
if not object_id(N'Tempdb..#TT') is null
drop table #TT
Go
Create table #TT([a] nvarchar(25),[b] nvarchar(29),[c] nvarchar(28),[d] nvarchar(29),[e] nvarchar(26),[f] nvarchar(29),[g] nvarchar(28),[h] nvarchar(24),[i] nvarchar(24))
Go
--测试数据结束
DECLARE @str NVARCHAR(MAX);
WITH cte
AS ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ( SELECT 0
) ) AS num
FROM #T
)
SELECT @str = ';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY(SELECT 0)) AS num FROM #T
) select a as [' + a + '], b as [' + b + '], c as [' + c + '], d as [' + d
+ '], e as [' + e + '], f as [' + f + '], g as [' + g
+ '], h as [' + h + '], g as [' + i + ']'
FROM cte a
WHERE a.num = 1
SET @str += ' from cte b where b.num<>1'
--插入临时表
INSERT INTO #TT EXEC(@str)
--读取临时表
SELECT * FROM #TT二月十六 2017-06-17
- 打赏
- 举报
可以，类似这样
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a] nvarchar(25),[b] nvarchar(29),[c] nvarchar(28),[d] nvarchar(29),[e] nvarchar(26),[f] nvarchar(29),[g] nvarchar(28),[h] nvarchar(24),[i] nvarchar(24))
Insert #T
select N'科室\大类',N'西药费',N'中成药费',N'中草药费',N'卫生材料费',N'中药饮片',N'院内制剂',N'无',N'无' union all
select N'放射科',N'117.42',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00' union all
select N'风湿科',N'125354.50',N'56513.34',N'154381.42',N'891.61',N'152991.38',N'10578.90',N'0.00',N'0.00'
if not object_id(N'Tempdb..#TT') is null
drop table #TT
Go
Create table #TT([a] nvarchar(25),[b] nvarchar(29),[c] nvarchar(28),[d] nvarchar(29),[e] nvarchar(26),[f] nvarchar(29),[g] nvarchar(28),[h] nvarchar(24),[i] nvarchar(24))
Go
--测试数据结束
DECLARE @str NVARCHAR(MAX);
WITH cte
AS ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ( SELECT 0
) ) AS num
FROM #T
)
SELECT @str = ';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY(SELECT 0)) AS num FROM #T
) select a as [' + a + '], b as [' + b + '], c as [' + c + '], d as [' + d
+ '], e as [' + e + '], f as [' + f + '], g as [' + g
+ '], h as [' + h + '], g as [' + i + ']'
FROM cte a
WHERE a.num = 1
SET @str += ' from cte b where b.num<>1'
--插入临时表
INSERT INTO #TT EXEC(@str)
--读取临时表
SELECT * FROM #TT不穿内裤的超人 2017-06-17
- 打赏
- 举报
谢谢你，我自己也搞定了，也就是拼接了。
declare @bt varchar(20),@sql NVARCHAR(MAX);
declare @med_01 varchar(50),@med_02 varchar(50),@med_03 varchar(50),@med_04 varchar(50),@med_05 varchar(50),@med_06 varchar(50),@med_07 varchar(50),@med_08 varchar(50)
select @bt='科室\大类',
@med_01=(select top_name from medtopclass where top_no='01'),
@med_02=(select top_name from medtopclass where top_no='02'),
@med_03=isnull((select top_name from medtopclass where top_no='03'),'无'),
@med_04=isnull((select top_name from medtopclass where top_no='04'),'无'),
@med_05=isnull((select top_name from medtopclass where top_no='05'),'无'),
@med_06=isnull((select top_name from medtopclass where top_no='06'),'无'),
@med_07=isnull((select top_name from medtopclass where top_no='07'),'无'),
@med_08='其它'
set @sql=N'
select z as '''+@bt+''',
isnull(SUM(a),0) as '+@med_01+',isnull(SUM(b),0) as '+@med_02+',isnull(SUM(c),0) as '+@med_03+',isnull(SUM(d),0) as '+@med_04+',
isnull(SUM(e),0) as '+@med_05+',isnull(SUM(f),0) as '+@med_06+',isnull(SUM(g),0) as '+@med_07+',isnull(SUM(h),0) as '+@med_08+'
from (
SELECT dept_name
,case when c.top_no=''01'' then sum(fee_money) end,case when c.top_no=''02'' then sum(fee_money) end
,case when c.top_no=''03'' then sum(fee_money) end,case when c.top_no=''04'' then sum(fee_money) end
,case when c.top_no=''05'' then sum(fee_money) end,case when c.top_no=''06'' then sum(fee_money) end
,case when c.top_no=''07'' then sum(fee_money) end
,case when c.top_no not in (''01'',''02'',''03'',''04'',''05'',''06'',''07'') then sum(fee_money) end
FROM alldiagnandfunc a,department b,medtopclass c
where a.position_id=''0'' and a.cur_date between ''20150317'' and ''20170517'' and
a.medorfee=''0'' and a.diagn_dept=b.dept_no and a.top_no=c.top_no group by dept_name,c.top_no)a(z,a,b,c,d,e,f,g,h)
group by a.z;'
EXEC sp_executesql @sql ;二月十六 2017-06-17
- 打赏
- 举报
用拼接字符串可以：
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a] nvarchar(25),[b] nvarchar(29),[c] nvarchar(28),[d] nvarchar(29),[e] nvarchar(26),[f] nvarchar(29),[g] nvarchar(28),[h] nvarchar(24),[i] nvarchar(24))
Insert #T
select N'科室\大类',N'西药费',N'中成药费',N'中草药费',N'卫生材料费',N'中药饮片',N'院内制剂',N'无',N'无' union all
select N'放射科',N'117.42',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00',N'0.00' union all
select N'风湿科',N'125354.50',N'56513.34',N'154381.42',N'891.61',N'152991.38',N'10578.90',N'0.00',N'0.00'
Go
--测试数据结束
DECLARE @str NVARCHAR(MAX);
WITH cte
AS ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ( SELECT 0
) ) AS num
FROM #T
)
SELECT @str = ';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY(SELECT 0)) AS num FROM #T
) select a as [' + a + '], b as [' + b + '], c as [' + c + '], d as [' + d
+ '], e as [' + e + '], f as [' + f + '], g as [' + g
+ '], h as [' + h + '], g as [' + i + ']'
FROM cte a
WHERE a.num = 1
SET @str += ' from cte b where b.num<>1'
EXEC(@str)
不穿内裤的超人 2017-06-17
- 打赏
- 举报
我的意思是，这个是我通过查询语句查询出来的，我不给列名数据库默认给【无列名】，这不是主要的问题，主要问题是我要把查询出来的第一行内容转换成列名
科室\大类 西药费 中成药费 中草药费 卫生材料费 中药饮片 院内制剂 无 无
放射科 117.42 0.00 0.00 0.00 0.00 0.00 0.00 0.00
风湿科 125354.50 56513.34 154381.42 891.61 152991.38 10578.90 0.00 0.00二月十六 2017-06-17
- 打赏
- 举报
无列名也得有列名吧？不然在数据库中怎么存储的？
