Crosstab, Pivot Query representation

Hello,

I need help with data representation.

I have a query :

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

The above query returns the following results:
USERLOGIN SOURCE DBUSERNAME

Don Crilly FC8 Don Crilly
Don Crilly ACT Donald Crilly
Don Crilly SFS Don Crilly

I need the output in following format:

USERLOGIN ACT FC8 SFS
-
Don Crilly Donald Crilly Don Crilly Don Crilly

Can you please guide me as to what I should do to achive the required results.

Thanks.

If you use SQL Server 2005:
SELECT * FROM (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) p
PIVOT (MIN(DBUSERNAME) FOR SOURCE IN ([FC8], [ACT], [SFS])) pvt
--Or You can use the following for earlier versions
select a.USERLOGIN
, min(case a.SOURCE when 'FC8' then DBUSERNAME end) as [FC8]
, min(case a.SOURCE when 'ACT' then DBUSERNAME end) as [ACT]
, min(case a.SOURCE when 'SFS' then DBUSERNAME end) as [SFS]
from (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) as a
group by a.USERLOGIN|||

If you're using SQL Server 2005, then you can use the PIVOT operator to do this.

For example, this will work for your query:

SELECT *

FROM (SELECT USERLOGIN, SOURCE, DBUSERNAME

FROM TBL_USER

WHERE USERLOGIN LIKE 'Don Crilly'

) SOURCEQUERY

PIVOT (

MIN(DBUSERNAME)

FOR SOURCE IN ([ACT], [FC8], [SFS])

) AS PIVOTTABLE

The restrictions for using the PIVOT operator are that the PIVOT clause must include an aggregate operator (the MIN in this case, which will work unless a single USERLOGIN and SOURCE combination can have multiple DBUSERNAMEs associated with it) and that the resulting column names (ACT, FC8 and SFS) must be listed explicitly (unlike MS Excel, for example, which just creates columns for every value in the pivotting column).

Hope that gives you a start at least.

Iain

|||

This query will do..

Select * from dbo.TBL_USER
PIVOT
(
Min(DBUserName) For Source in ([FC8],[ACT],[SFS])
) as T
Where UserLogin = 'Don Crilly'

|||Thanks a lot :)

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

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..

Cross Tab/Pivot table/UDF?

I have the following tables:

tbTemplateShapeProperties
fkTemplate | fkProperty | PropertyValue
---------------
1 | 1 | 192
1 | 2 | 36
1 | 3 | 4
1 | 4 | 5
1 | 5 | 2

tbShapeProperties
Property | PropertyName | fkShape
--------------
1 | Width | 1
2 | Height | 1
3 | Flange | 1
4 | Avg. Leg Width | 5
5 | Leg Count | 2

From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF

XSection (Width, Height, Flange, Leg, LegCount)

I tried the following to get a pivot table but it does not give a single row but 5.

SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width,
CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height,
CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange,
CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg,
CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = 1

The following results are returned:

Width | Height | Flange | Leg | LegCount
--------------
192 | 0 | 0 | 0 | 0
0 | 36 | 0 | 0 | 0
0 | 0 | 6 | 0 | 0
0 | 0 | 0 | 5 | 0
0 | 0 | 0 | 0 | 2

The desired result as you could guess is:

Width | Height | Flange | Leg | LegCount
--------------
192 | 36 | 6 | 5 | 2

So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?

Any hints?

Mike BYeah, I did it!!!

Here is the code:

CREATE PROCEDURE usp_GetDoubleTCrossSection

@.iTemplate int,
@.fXSection float OUTPUT

AS

declare @.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float

set @.iTemplate = 1
SELECT @.fWidth = SUM(CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ElSE 0 END),
@.fHeight = SUM(CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ElSE 0 END),
@.fFlange = SUM(CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ElSE 0 END),
@.fLegWidth = SUM(CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ElSE 0 END),
@.fLegs = SUM(CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ElSE 0 END)
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = @.iTemplate

SELECT @.fXSection = [dbo].[TEE_XSECTION](@.fWidth, @.fHeight, @.fFlange, @.fLegWidth, @.fLegs)
GO

//UDF
CREATE FUNCTION TEE_XSECTION
(@.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float)

RETURNS float

AS
BEGIN
RETURN (@.fWidth * @.fFlange + (((@.fHeight - @.fFlange)*@.fLegWidth)*@.fLegs))
END

No cursor!!! Whew!

Mike B

Cross Reference or Pivot table

Hi All,

The problem is about cross reference.

1. I have a third party cross reference store procedure SimpleXTab

CREATE PROCEDURE [dbo].[SimpleXTab2]
@.XField varChar(50),
@.XTable varChar(100),
@.XWhereString varChar(250),
@.XFunction varChar(10),
@.XFunctionField varChar(50),
@.XRow varchar(300),
@.ResultTable varchar(100)
AS
Declare @.SqlStr nvarchar(4000)
Declare @.tempsql nvarchar(4000)
Declare @.SqlStrCur nvarchar(4000)
Declare @.col nvarchar(100)

set @.SqlStrCur = N'Select [' + @.XField + '] into ##temptbl_Cursor from [' + @.XTable + '] ' + @.XWhereString + ' Group By [' + @.XField + ']'

/* select @.sqlstrcur */
exec sp_executesql @.sqlstrcur

declare xcursor Cursor for Select * from ##temptbl_Cursor

open xcursor

Fetch next from xcursor
into @.Col

While @.@.Fetch_Status = 0
Begin
set @.Sqlstr = @.Sqlstr + ", "
set @.tempsql = isnull(@.sqlstr,'') + isnull(@.XFunction + '( Case When ' + @.XField + " = '"+@.Col +
"' then [" + @.XFunctionField + "] Else 0 End) As [" + @.Col + "]" ,'')
set @.Sqlstr = @.tempsql
Fetch next from xcursor into @.Col

End

/* Select @.Sqlstr as [mk], len(@.sqlstr) as [leng] */

set @.tempsql = 'Select ' + @.XRow + ', ' + @.Sqlstr + 'into '+@.ResultTable+' From ' + @.XTable +
@.XWhereString + ' Group by ' + @.XRow
print @.tempsql
set @.Sqlstr = @.tempsql

Close xcursor
Deallocate xcursor

set @.tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @.tempsql
print @.tempsql
/* Select @.Sqlstr as [mk], len(@.sqlstr) as [leng] */
print @.sqlstr
exec sp_executesql @.Sqlstr

if @.@.rowcount = 0 select 'No Records found'
GO

2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function.
how can i modify SimpleXtab to avoid using math function but still can generate cross reference.

exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'

Many Thanks!

no replying. I didn't describe clearly?

|||

What did you want it to do with multiple values? If there is only one value per row/column, use MAX or MIN.