Cross Tab, Pivot

[Lilian]

I am trying to find SQL-Server equivalent to the access
Transform and Pivot. I have a table in the following
structure:

Key Field_Type Field_Value
P1 CourseID 1000
P2 StudentID ABC
P3 CourseID 2001
P4 StudentID EFT

I need to transpose the above into the following without
knowing in advance the amount of distinct values in the
column Field_Type --> in this case there are two (CourseID
and StudentID)
CourseID StudentID
1000 ABC
2001 EFT

[Hirantha S. Hettiarachchi]

Please post DDL . Maybe someone here will point you to a solution. but if
you are looking for pivoting in sql server, look here
http://rac4sql.home.attbi.comn

--
hth
Hirantha S Hettiarachchi MCSD,MCDBA

Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Lilian" <lch...@hotmail.com> wrote in message
news:856801c19d3c$60af8a00$a5e62ecf@tkmsftngxa07...

[Dinesh T K]

>>>http://rac4sql.home.attbi.comn

ohh yes....Oj just made his master proud !!another follower :-)

Dinesh.

"Hirantha S. Hettiarachchi" <hira...@nospam.com> wrote in message
news:#7NXeiUnBHA.1948@tkmsftngp03...

[Hirantha S. Hettiarachchi]

heh heh :)

--
hth
Hirantha S Hettiarachchi MCSD,MCDBA

Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Dinesh T K" <dine...@usa.net> wrote in message
news:emx0QpUnBHA.1592@tkmsftngp05...

[oj]

now, now there is master/slave here...only primary key and foreign key. :)

you need both to maintain *data* integrity. hehehhe...

--
-oj
http://rac4sql.home.attbi.com

"Dinesh T K" <dine...@usa.net> wrote in message
news:emx0QpUnBHA.1592@tkmsftngp05...

[oj]

bummer, missing out the *no* in the last post.. :)

so, here is the repeat...

now, now there is NO master/slave here...only primary key and foreign key.
:)

--
-oj
http://rac4sql.home.attbi.com

[Keith Tate]

http://rac4sql.home.attbi.com
(minus the trailing n)

"Hirantha S. Hettiarachchi" <hira...@nospam.com> wrote in message
news:#7NXeiUnBHA.1948@tkmsftngp03...

[Joe Celko]

Stop wht you are doing and redesign your database now.

You are trying to do an OO metadata design in SQL. It does not work.
In terms of a DATA model, explain what things in the reality you are
modeling map to your columns. Your names do not make sense as data
elements.

You can fake this with elaborate dynanmic SQL under the covers. It will
run like glue, not scale, and fall apart in a year. I watched two
companies destroy their database projects with this design from the
inside.

Read a good book on Relational design and look at the INCITS L8 Metadata
Committtee rules for naming data elements.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Greg Obleshchuk]

A lonely voice in the crowd . Why not try xp_ags_crosstab, it's quick
scales really well (1000's of columns in crosstab) and runs like a dream, if
I do say so my self.
http://www.ag-software.com/AGS/xp_ags_crosstab.asp
:)

--
I hope this helps
regards
Greg O MCSD

"Hirantha S. Hettiarachchi" <hira...@nospam.com> wrote in message
news:#7NXeiUnBHA.1948@tkmsftngp03...

[BP Margolin]

Lilian,

Start by looking at:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574

The solution in the KB article is useful if you know ahead of time the
number of columns.

If you do not know ahead of time the number of column, you might try the
suggestion that follows that was originally posted by Access MVP Mary
Chipman:

Transact-SQL doesn't support the syntax for creating crosstab queries.
if you create a crosstab query in access, you can always run it by
using the OpenQuery syntax from sql server if you have linked your
access database as a linked server. in the following example AccessDB
is the name of the linked server:

SELECT * FROM OPENQUERY(AccessDB,
'TRANSFORM Count(tblOrder.OrderID) AS CountOfOrderID
SELECT [LastName] & ", " & [Firstname] AS Name
FROM tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerID =
tblOrder.CustomerID
GROUP BY [LastName] & ", " & [Firstname]
PIVOT Format([OrderDate],"mm/yyyy")')

Finally, you might also take a look at:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

-------------------------------------------
BP Margolin

Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Lilian" <lch...@hotmail.com> wrote in message
news:856801c19d3c$60af8a00$a5e62ecf@tkmsftngxa07...

[Steve Kass]

Lilian,

Here's another solution to creating a cross-tab or pivot table
when you don't know the names or number of columns in
the result.

create procedure sk_rename_column (
@table sysname,
@oldname sysname,
@newname sysname
) as
DBCC RENAMECOLUMN(@table,@oldname,@newname)
go

set nocount on
create table Sales (
AccountCode char(5),
Category int,
Amount decimal(8,2)
)

--Populate table with sample data
insert into Sales
select customerID, EmployeeID, sum(Freight)
from Northwind.dbo.orders
group by customerID, EmployeeID

create unique clustered index Sales_AC_C
on Sales(AccountCode,Category)

--Create table to hold data column names and positions
select A.Category,
count(distinct B.Category) AS Position
into #columns
from Sales A join Sales B
on A.Category >= B.Category
group by A.Category

create unique clustered index #columns_P on #columns(Position)
create unique index #columns_C on #columns(Category)

--Generate first column of Pivot table
select distinct AccountCode into Pivot from Sales

--Find number of data columns to be added to Pivot table
declare @datacols int
select @datacols = max(Position) from #columns

--Add data columns one by one in the correct order
declare @i int
set @i = 0
while @i < @datacols begin
set @i = @i + 1

--Add next data column to Pivot table
select P.*, isnull((
select Amount
from Sales S join #columns C
on C.Position = @i
and C.Category = S.Category
where P.AccountCode = S.AccountCode),0) AS X
into PivotAugmented
from Pivot P

--Name new data column correctly
declare @c sysname
select @c = Category
from #columns
where Position = @i
exec sk_rename_column '[dbo].[PivotAugmented]', 'X', @c

--Replace Pivot table with new table
drop table Pivot
select * into Pivot from PivotAugmented
drop table PivotAugmented
end

select * from Pivot
go

drop table Pivot
drop table #columns
drop table Sales
drop procedure sk_rename_column

Steve Kass
Drew University

[Steve Dassin]

"BP Margolin" <bpm...@attglobal.net> wrote in message
news:uuVG6wYnBHA.2284@tkmsftngp05...

> If you do not know ahead of time the number of column, you might try the
suggestion >that follows that was originally >posted by Access MVP Mary
Chipman:

>.
>.

http://groups.google.com/groups?hl=en&threadm=19990726123506.01438.00002091%
40ng-cq1.aol.com&rnum=9&prev=/groups%3Fq%3Dtrysql%2540aol.com%2Bopenquery%2B
transform%26hl%3Den%26scoring%3Dd%26selm%3D19990726123506.01438.00002091%254
0ng-cq1.aol.com%26rnum%3D9

Hmm.... -:)