Crosstab result

Hi there,

I'm using sql2005. I've got a table in tabular form and I need to convert it to crosstab format. I've tried to use pivot operator but i can't get the result i want. Because pivot operator needs to aggregate a field but what i need is Dim values as column names and DimCode as values within those columns as it is, no aggregations.

For example : This is tabular table.

Dim DimCode JrnlEntry GLDistId TrxDate GLPostDT TrxType AccountIndex DebitAmt CreditAmt Amount 4 2 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 11991 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 7 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 5 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 1 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 5 5 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 4 12821 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 5 4 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 4 3 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500 5 8 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500

And i want the end result as under..

jrnentry aaGLDistID TRXDATE GLPOSTDT aaTRXType Dim1 Dim2 Dim3 Dim4 Dim5 Dim6 Dim7 Dim8 Dim9 Dim10 ACTINDX DEBITAMT CRDTAMNT Amount 54222 1 25/05/2007 25/05/2007 1 0 0 0 2 7 0 0 0 0 0 44256 617 0 617 54222 1 25/05/2007 25/05/2007 1 0 0 0 11991 5 0 0 0 0 0 44256 617 0 617 54225 1 31/05/2007 31/05/2007 1 0 0 0 1 5 0 0 0 0 0 44256 600 0 600 54225 2 31/05/2007 31/05/2007 1 0 0 0 12821 4 0 0 0 0 0 44256 400 0 400 54227 3 31/05/2007 31/05/2007 1 0 0 0 3 8 0 0 0 0 0 44256 0 500 -500

Your help will much appreciated.

There is an excellent article about PIVOT by Peter Larsson.

Follow the link :

Pivot table for Microsoft SQL Server

Thanks

Naras.

|||

For quite a few examples of using PIVOT, do a search on this Forum for the keyword 'Pivot'. Amazing how often this question is asked, and how often folks don't seem to bother looking in the archives to find their answer.

|||

Thanks Naras,

I'm not looking for help in Pivot functionality of sql2005 as it won't be usefull for me. If you look at the first table, i want the first column Dim to be used for the new columns i.e. Dim1, Dim2, Dim3 will be columns in the crosstab result but then i want DimCode to appear under respective columns without any aggregations. In pivot you've to use aggregate function. Also in crosstab result if look at the record with jrnentry 54222, there are two lines with the same aaGLDistId i.e. i don't want to aggregate values within dim1, dim2 etc.

Thanks

Vivek

|||

Vivek,

You are wrong. You can achive it using the PIVOT Operator. See the below query..

Code Snippet

Create Table #data (

[Dim] int ,

[DimCode] int ,

[JrnlEntry] int ,

[GLDistId] int ,

[TrxDate] datetime ,

[GLPostDT] datetime ,

[TrxType] int ,

[AccountIndex] int ,

[DebitAmt] int ,

[CreditAmt] int ,

[Amount] int

);

Set Dateformat DMY

Insert Into #data Values('4','2','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','11991','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','7','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','5','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','1','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('5','5','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('4','12821','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('5','4','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('4','3','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Insert Into #data Values('5','8','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Select

[JrnlEntry]

,[GLDistId]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,Isnull([1],0) Dim1

,Isnull([2],0) Dim2

,Isnull([3],0) Dim3

,Isnull([4],0) Dim4

,Isnull([5],0) Dim5

,Isnull([6],0) Dim6

,Isnull([7],0) Dim7

,Isnull([8],0) Dim8

,Isnull([9],0) Dim9

,Isnull([10],0) Dim10

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount] From

(

Select

[Dim]

,[DimCode]

,[GLDistId]

,[JrnlEntry]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount]

,Row_Number() Over (Partition By [Dim],[GLDistId],[JrnlEntry] Order By [Dim],[GLDistId],[JrnlEntry]) RowId

From

#data

) as Data

Pivot

(

Max([DimCode])

For [Dim] in

(

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

)

) As PVT

|||

Thanks Manivannan, much appreciated. Its returning correct result. I almost spent the whole day on this yesterday with frustration.

Regards,

Vivek

cross-tab reporting?

Hello:
Is it possible to create cross-tab reports in SQL Reporting Services,
similar to the cross-tab reports that can be created in Crystal Reports?
Thanks!
childofthe1980sAre you new to SSRS, it can be done very much, In SSRS its called as matrix
Reports.
Amarnath
"childofthe1980s" wrote:
> Hello:
> Is it possible to create cross-tab reports in SQL Reporting Services,
> similar to the cross-tab reports that can be created in Crystal Reports?
> Thanks!
> childofthe1980s

Crosstab Report

I am creating report with total costs by job, it repeats "column totals"
two times, and at the end column "grand total". "grand total" column is okay.
Is there any way I can drop/disable repeating "column totals". See example below, help me to drop repeating column.

Thanks

Job 1 Job 2 GrandTotal
Total Total

Cost 1 177,855 177,855 162,755 162,755 340,610
Cost 2 169,847 169,847 161,063 161,063 330,910
Cost 3 37,404 37,404 102,416 102,416 139,820In CR,right click on the cross tabl. Choose format Cross tabl. U ve a tab like "Customize Style". Choose each and every row in Rows List and below that u ve Grouping Option. Suppres that grouping option. I think this may be a soln. Try it out.

Crosstab question for a "properties" table

Most of the examples for crosstabs use pivot. It looks like aggregations are what they are used for. What about if I have a dynamic properties table. So there is a table Person and a table Property and a table PropertyPersonValue. Is there any way to create a crosstab table of Person with Properties listed in the column heading and the Values in the cells?

Sure, can you post some DDL and sample data?

AMB

|||

CREATE TABLE Person

(

PersonID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonID)

)

GO

CREATE TABLE PersonProperty

(

PersonPropertyID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_PersonProperty] PRIMARY KEY CLUSTERED (PersonPropertyID)

)

GO

CREATE TABLE PersonPropertyValue

(

PersonID INT NOT NULL,

PersonPropertyID INT NOT NULL,

[Value] VARCHAR(1000),

CONSTRAINT [PK_PersonPropertyValue] PRIMARY KEY CLUSTERED (PersonID,PersonPropertyID),

CONSTRAINT [FK_PersonPropertyValue_PersonID] FOREIGN KEY (PersonID)

REFERENCES [Person],

CONSTRAINT [FK_PersonPropertyValue_PersonPropertyID] FOREIGN KEY (PersonPropertyID)

REFERENCES [PersonProperty],

)

GO

INSERT INTO PersonProperty ([Name]) VALUES ('Eye Color')

INSERT INTO PersonProperty ([Name]) VALUES ('Title')

INSERT INTO Person ([Name]) VALUES ('Joe Smith')

INSERT INTO Person ([Name]) VALUES ('Fred Thompson')

INSERT INTO Person ([Name]) VALUES ('Sally Hamilton')

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Brown'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Blue'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Green'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Eye Color'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'CFO'

FROM Person p,PersonProperty pp

WHERE p.Name='Joe Smith'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Vice Presient'

FROM Person p,PersonProperty pp

WHERE p.Name='Fred Thompson'

AND pp.Name='Title'

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Sr. Partner'

FROM Person p,PersonProperty pp

WHERE p.Name='Sally Hamilton'

AND pp.Name='Title'

SELECT p.Name,pp.Name AS PropertyName, ppv.Value

FROM Person p

LEFT JOIN PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

|||

Try:

declare @.columns nvarchar(max)

declare @.sql nvarchar(max)

set @.columns = stuff(

(

select ',' + cast(quotename([Name]) as nvarchar(max))

from dbo.PersonProperty

order by [Name]

for xml path('')

), 1, 1, '')

set @.sql = N'

select

*

from

(

SELECT

p.Name,pp.Name AS PropertyName, ppv.Value

FROM

dbo.Person p

LEFT JOIN

dbo.PersonPropertyValue ppv

ON p.PersonID=ppv.PersonID

LEFT JOIN

dbo.PersonProperty pp

ON pp.PersonPropertyID=ppv.PersonPropertyID

) as src

pivot

(

min(Value)

for PropertyName in (' + @.columns + N')

) as pvt

order by

[Name]

'

exec sp_executesql @.sql

go

Result:

Name Eye Color Title Fred Thompson Blue Vice Presient Joe Smith Brown CFO Sally Hamilton Green Sr. Partner

Be careful with sql injection.

PIVOT on Steroids

http://www.sqlmag.com/Articles/ArticleID/94268/pg/3/3.html

AMB

|||Thanks. So I guess pivot is not really used for this sort of thing.

|||

Nope, you are wrong.

He used the PIVOT operator, but he listed or collected all the property values on one variable and given those stored values to your PIVOT operator query...

Print @.SQL

You will get the query which is executed here..

Crosstab query(intersection table)?

I can use crosstab query(intersection table) in sql server.

for example :

CREATE TABLE [Test] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike,N'math,60)

INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'English',70)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'france',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'jane',N'English',75)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mary',N'france',57)

INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'math',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'Englist',100)

Go

--

declare @.sql varchar(8000)

set @.sql = 'select name,'

select @.sql = @.sql + 'sum(case subject when ''+subject+''

then source else 0 end) as ''+subject+'','

from (select distinct subject from test) as a

select @.sql = left(@.sql,len(@.sql)-1) + ' from test group by name'

exec(@.sql)

But I cannot use it in sql server mobile editon.

HOw do I? thank you very much!!!

Suggest you review the SQL Mobile Books OnLine - there is a complete "SQL Reference for SQL Mobile" reference chapter there that will outline your options. The support for the CASE function in SQL Mobile is more basic than what you are trying to do here.

Darren

|||

Thank you, Darren Shaffer.

I do review SQL Reference for SQL Mobile. But I cannot find the method in sql mobile.

Yes , It can work well use case function . But the colums is not determinate. so I have to use :

declare @.sql varchar(8000)

set @.sql = 'select name,'

select @.sql = @.sql + 'sum(case subject when ''+subject+''

......////

The query can work well in sql server , but it cannot work in sql server mobile.

How do I ?

我非常困惑，请帮助解决好吗？谢谢了。

|||

I try to use crosstab query(intersection table) by sql server mobile in .netCF in my application. But write crosstab query is very hard for me in sql mobile editon.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277223&SiteID=1&mode=1

Must it be not carried out throuth sql query.May be it can do throught programming ？If that , How can I do it ? By the way ,I can only use C#.

|||

My advice would be to make the determination of the appropriate query to run in your application code (not in SQL) and then submit the appropriate query string to SQL Mobile.

Darren

|||

Moving this thread to the SQL Mobile forum.

I am also going to merge it with the other thread.

Cross-tab query in SQL Server

I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.

How would I create a cross-tab query in SQL Server?

Table for input to cross-tab query: [Inspection Data]

SerialNumber ParamName Result Pass/Fail

001 Test1 3.43 Pass

001 Test2 3.27 Pass

001 Test3 2.97 Pass

002 Test1 2.88 Pass

002 Test2 3.01 Pass

002 Test3 4.22 Fail

003 Test1 3.11 Pass

003 Test2 2.91 Pass

003 Test3 3.28 Pass

Cross-tab Query Output:

Test1 Test2 Test3 Pass/Fail

001 3.43 3.27 2.97 Pass

002 2.88 3.01 4.22 Fail

003 3.11 2.91 3.28 Pass

Thanks,

Sam

SELECT b.sno, b.test1, b.test2, b.test3, c.passfail FROM (select a.sNo
, min(case a.pName when 'test1' then result end) as [test1]
, min(case a.pName when 'test2' then result end) as [test2]
, min(case a.pName when 'test3' then result end) as [test3]
from dbo.pivotTable$ as a
group by a.sNo) b INNER JOIN (SELECT sNo, min(pOrf) AS PassFail
FROM pivotTable$ GROUP BY sNo) c ON c.sNo=b.sNo

For SQL Server 2005, you can:
SELECT p.sno, p.test1, p.test2, p.test3, b.PassFail
FROM (SELECT sno, pName, result
from pivotTable ) t
pivot (MAX(t.result) for t.pName in (test1,test2,test3)) AS p
INNER JOIN (SELECT sNo, min(pOrf) AS PassFail
FROM pivotTable$ GROUP BY sNo) b ON p.sNo=b.sNo|||

I am having trouble getting the syntax correct when I try to implement the real query. It tells me "Incorrect syntax near the keyword 'ON'

Do you have any idea what it might be?

select b.[Work Order No], b.[Serial No], b.[Apex], b.[Height]], b.[Radius], b.[Visual], b.[IL], b.[BR], c.[PF]

from ((select a.[Work Order No], a.[Serial No]

, min(case a.[Parameter Name] when 'Apex Offset' then [Value] end) as [Apex]

, min(case a.[Parameter Name] when 'Fiber Height ( Spherical Fit )' then [Value] end) as [Height]

, min(case a.[Parameter Name] when 'Radius of Curvature' then [Value] end) as [Radius]

, min(case a.[Parameter Name] when 'Visual Inspection' then [Value] end) as [Visual]

, min(case a.[Parameter Name] when 'Insertion Loss' then [Value] end) as [IL]

, min(case a.[Parameter Name] when 'Back Reflection' then [Value] end) as [BR]

from dbo.[Inspection Data] as a

group by a.[Work Order No],[Serial No] ) b inner join ((select [Work Order No], [Serial No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No],[Serial No] ) c on c.[Work Order No]=b.[Work Order No] and c.[Serial No] = b.[Serial No]

Thank you,

Sam

|||

"...

group by a.[Work Order No],[Serial No] ) b inner join ((select [Work Order No], [Serial No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No],[Serial No] ) c on c.[Work Order No]=b.[Work Order No] and c.[Serial No] = b.[Serial No]"

Is this an extra parathenses (in red)?

CROSSTAB QUERY FROM ACCESS TO SQL

Hello,
I'm quite basically new to working with SQL databases, coming from a
Microsoft Access background. As we've got the SQL server sitting here,
I've been asked to get the MS Access db's moved across onto it. I've
had no problem with the tables, but as the majority of the reporting
comes from cross tab queries I'm having trouble getting started with
the design of a cross tab/pivot view. I've tried all the examples the
search on groups provided but I'm having a hard time making it work for
me.
I'm reporting off one table tblMONTHCOSTS, the data at the moment is
displayed as
txtMOBILE NO txtBILLMONTH curCALLCOST
078******** 200501 15.25
077******* 200501 12.54
078******** 200502 9.54
077******* 200502 10.55
And so on...... For 12 months, for 39 mobile phones.
What I want it to do is display like this :
txtMOBILE NO 200501 200502 200503......
078******** 15.25 9.54
077******* 12.54 10.55
Does anyone have any advice on how this can be done? I know I'm
pretty new to this so if you could even just point me in the direction
of a good book I would be very grateful.
ThanksRefer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx
Madhivanan