在SqlServer中如何在水平方向进行表设计 [英] In SqlServer How to arrage table design in Horizontal

问题描述

亲爱的朋友，

在这里，我的数据库sqlserver中有一个问题，我的表设计是这样的

表格:

txtAge总计
低于6 3
6 7
7 11
8 18
9 19


现在我想这样显示我的桌子

txage:Below6 6 7 8 9
总数:3 7 11 18 19

如何更改为水平

示例iam给出我的查询:



SELECT ntblAge.txtAge，tblPrimaryStandard1RegistrationByAge.intMale + tblPrimaryStandard1RegistrationByAge.intFemale AS Total
FROM tblPrimaryStandard1RegistrationByAge INNER JOIN
ntblAge ON tblPrimaryStandard1RegistrationByAge.intAgeId = ntblAge.intAgeId内部联接
tblSchool ON tblPrimaryStandard1RegistrationByAge.intSchoolID = tblSchool.intSchoolID
在哪里(tblPrimaryStandard1RegistrationByAge.intSchoolID = 58)


请解决这个问题.


问候，

Anilkumar.D

Dear Friends,

Here i have a Problem in my database sqlserver my table design is like this

Table:

txtAge Total
Below6 3
6 7
7 11
8 18
9 19


Now i want to Display my table like this

txage:Below6 6 7 8 9
Total: 3 7 11 18 19

How can change to horizontal

Sample iam giving my query:



SELECT ntblAge.txtAge, tblPrimaryStandard1RegistrationByAge.intMale + tblPrimaryStandard1RegistrationByAge.intFemale AS Total
FROM tblPrimaryStandard1RegistrationByAge INNER JOIN
ntblAge ON tblPrimaryStandard1RegistrationByAge.intAgeId = ntblAge.intAgeId INNER JOIN
tblSchool ON tblPrimaryStandard1RegistrationByAge.intSchoolID = tblSchool.intSchoolID
WHERE (tblPrimaryStandard1RegistrationByAge.intSchoolID = 58)


Please solve this .


Regards,

Anilkumar.D

推荐答案

对此查询不可能，因此必须编写自己的存储过程.

例子
创建名称为tlbTextData的表

It is not possible with the query for this u have to write your own store procedure.

Example
Create Table of name tlbTextData

create table tlbTextData (fldTextKey varchar(10), fldTextVal varchar(10))
insert into tlbTextData values('One','1')
insert into tlbTextData values('Two','2')
insert into tlbTextData values('Three','3')
insert into tlbTextData values('Four','4')
insert into tlbTextData values('Five','5')

受约束的程序

Strored Procedure

DECLARE @ColNameAS VARCHAR(10),
		@RowElement AS VARCHAR(10),
		@SqlQuery AS NVARCHAR(300),
		@iColumnName AS INT,
		@iFirstTime AS INT,
		@AddColName AS NVARCHAR(10)

SELECT @iFirstTime=0

CREATE TABLE #tlbMemTable (fldColName VARCHAR(10))
   
DECLARE GetColumnName CURSOR FOR      
           SELECT column_name FROM Information_Schema.Columns WHERE table_name='tlbTextData'
OPEN GetColumnName      
FETCH NEXT FROM GetColumnName INTO @ColName     
WHILE(@@FETCH_STATUS = 0)    
BEGIN--1 
	INSERT INTO #tlbMemTable(fldColName) VALUES(@ColName)
	SET @iColumnName = 0 
	SELECT @SqlQuery = 'DECLARE GetRow CURSOR FOR SELECT '+ @ColName +' FROM tlbTextData' 
	EXEC sp_executesql @SqlQuery   
	OPEN GetRow
	FETCH NEXT FROM GetRow INTO @RowElement 
	WHILE(@@FETCH_STATUS = 0)    
	BEGIN--2 
		
		SELECT @AddColName = 'VAL'+CAST(@iColumnName AS VARCHAR(10))
		print @AddColName
		IF(@iFirstTime=0)
		BEGIN
			SELECT @SqlQuery = 'ALTER TABLE #tlbMemTable ADD '+@AddColName+' NVARCHAR(10)'
			EXEC(@SqlQuery)
		END
		
		SELECT @SqlQuery = 'UPDATE #tlbMemTable SET '+@AddColName+'='''+@RowElement+''''+' WHERE fldColName='''+@ColName +''''
		EXEC(@SqlQuery)		
		SET @iColumnName = @iColumnName + 1
		FETCH NEXT FROM GetRow INTO @RowElement 
	END--2
	CLOSE GetRow      
	DEALLOCATE GetRow
	SET @iFirstTime = 1
	FETCH NEXT FROM GetColumnName INTO @ColName   
END--1      
CLOSE GetColumnName      
DEALLOCATE GetColumnName   

SELECT * FROM #tlbMemTable
DROP TABLE #tlbMemTable

注意:如果您要使用它，请优化上述sp:)

Note : Please optimize above sp if ur going to use it :)

这篇关于在SqlServer中如何在水平方向进行表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！