Row values into columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Row values into columns

Author

Topic

itsmeeh
Starting Member

7 Posts

Posted - 2003-06-11 : 14:59:49

Hello. I'm new to Sql and i'm trying to understand cursors and if it is the right solution to what i'm trying to solve. I have 2 tables:
aCompany and raCompanyaCampus. aCompany contains CompanyID and CompanyName. raCompanyaCampus contains CompanyID and CampusID. Now CampusID is the same as CompanyID (A company can have more than 1 campus). Currently I get the following result set:

CompanyID | CompanyName | CampusID
1 | XXX | 20
1 | XXX | 30
1 | XXX | 40
2 | YYY | 60
2 | YYY | 70

I'm trying to get the following result set:

CompanyID | CompanyName | Campus1 | Campus2 | Campus3 | Campus4
1 | XXX | 20 | 30 | 40 | NULL
2 | YYY | 60 | 70 | NULL | NULL

Right now, the rows for CompanyID are duplicated for the number of Campus' there are. This is the script I currently have that doesn't work. Any suggestions would be greatly appreciated. Thanks in advance.

CREATE TABLE #campusid(companyid int, campusid int)
DECLARE @coid int
DECLARE @campus_count int
DECLARE @campus1 int
DECLARE @campus2 int
DECLARE @campus3 int
DECLARE @campus4 int

SET @campus_count=0
DECLARE campusidcursor cursor scroll keyset for

SELECT ac.companyid,ac2.companyid campid
FROM acompany AS ac
INNER JOIN racompanyacampus AS racac
ON racac.companyid=ac.companyid
INNER JOIN acompany AS ac2
ON ac2.companyid=racac.campusid
ORDER BY ac.companyid

OPEN campusidcursor
FETCH campusidcursor INTO @coid

WHILE (@@fetch_status = 0 )
BEGIN
SET @coid = CAST ((@campus_count + 1) AS VARCHAR)
SET @campus_count = @campus_count + 1

IF @campus_count<= 4
BEGIN
SET @campus4 = @coid
SET @campus3 = @coid
SET @campus2 = @coid
SET @campus1 = @coid
END
ELSE
IF @campus_count<= 3
BEGIN
SET @campus4 = null
SET @campus3 = @coid
SET @campus2 = @coid
SET @campus1 = @coid
END
IF @campus_count<= 2
BEGIN
SET @campus4 = null
SET @campus3 = null
SET @campus2 = @coid
SET @campus1 = @coid
END
IF @campus_count<= 1
BEGIN
SET @campus4 = null
SET @campus3 = null
SET @campus2 = null
SET @campus1 = @coid
END

FETCH NEXT FROM campusidcursor INTO @coid
END
CLOSE campusidcursor

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 15:14:11

This is what I refer to as a "fixed cross tab." the column headers are constants, but the values in them can change.

The solution is below. it is a little complex because the first step is to assign a "CampusNumber" to each campus for each company. this is done by using a "rolling total" technique.

Then, using that campusNumber, we do a cross tab and return the results.


declare @t table (CompanyID int, companyName char(3), CampusID char(2))

insert into @t
select 1,'XXX',20 union
select 1,'XXX',30 union
select 1,'XXX',40 union
select 2,'YYY',60 union
select 2,'YYY',70 

select companyID, companyName,
   MAX(case when CampusNumber = 1 then CampusID else Null END) as Campus1,
   MAX(case when CampusNumber = 2 then CampusID else Null END) as Campus2,
   MAX(case when CampusNumber = 3 then CampusID else Null END) as Campus3,
   MAX(case when CampusNumber = 4 then CampusID else Null END) as Campus4
FROM
(
select companyID, companyname, campusID,
  (select count(*) from @t b where b.companyID = a.companyID and b.campusID <= a.campusID) as CampusNumber
from
@t a
)
B
GROUP BY companyID, companyName

Kind of complex, but you can learn like 4 different techiniques all
from this 1 example. THIS would be a good recruiter question!

- Jeff

Edited by - jsmith8858 on 06/11/2003 15:15:33

itsmeeh
Starting Member

7 Posts

Posted - 2003-06-11 : 16:31:39

Thanks Jeff. I will incorporate this into my stored procedure. I'll let you know how I do.

Thanks for teaching me something NEW!

Jen

itsmeeh
Starting Member

7 Posts

Posted - 2003-06-16 : 12:33:39

Hello again.

Ok i've incorporated this with the tables I have. The problem i'm having now is that it's not giving me the result set I want. The company name is duplicating to the number of campuses. For example:

CompanyID | CompanyName | Campus1 | Campus2 | Campus3 | Campus4
101 | Southwest | 59 | NULL | NULL | NULL
101 | Southwest | NULL | 1569 | NULL | NULL

I want the following result set:

CompanyID | CompanyName | Campus1 | Campus2 | Campus3 | Campus4
101 | Southwest | 59 | 1569 | NULL | NULL

How do I get companyname to appear only once? Any suggestions:

declare @campus table (CompanyID int, CompanyName varchar(200), CampusID int)

insert into @campus

select racac.CompanyId, ac.Companyname, CampusID
from
racompanyaCampus as racac
inner join acompany as ac
on ac.companyId=racac.companyid
inner join acompany as ac2
on ac2.companyid=racac.campusid
order by
ac.companyName

select CompanyID, CompanyName,
MAX(case when CampusNumber = 1 then CampusID else Null END) as Campus1,
MAX(case when CampusNumber = 2 then CampusID else Null END) as Campus2,
MAX(case when CampusNumber = 3 then CampusID else Null END) as Campus3,
MAX(case when CampusNumber = 4 then CampusID else Null END) as Campus4
FROM
(
select CompanyID, CompanyName, CampusID,
(select count(*) from @campus b where b.companyID = a.companyID and b.campusID <= a.campusID) as CampusNumber
from
@campus a
)
b
GROUP BY CompanyID, CompanyName, CampusID

Thanks so much

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-16 : 13:00:01

Don't GROUP BY CampusID.

itsmeeh
Starting Member

7 Posts

Posted - 2003-06-16 : 13:26:51

Thanks Robvolk. That was pretty simple.

Sorry for posting it twice. Fairly new at this forum.

Thanks again for your help!

Jen

itsmeeh
Starting Member

7 Posts

Posted - 2003-06-19 : 15:37:08

Hello. I have the same script above where I have modified it to work with with our database. The problem now that i'm encountering is I want to be able to include not only all the Campus' a Company has but also their addresses for each. I'm not too familiar with fixed cross tabs. Does anyone have any suggestions how I can include the campus' address information and not the company's? I want to get the following result set:

CompanyID | Campus1 | Campus1Street | Campus2 | Campus2Street |
24 | 15 | 123 Street Ave | 18 | 567 Road|

Thanks in advance. Please see script below:

DECLARE @campus TABLE (CompanyID INT, CampusID INT, CampusName VARCHAR(200), CampusStreet VARCHAR(200), CampusCity VARCHAR(50), CampusState VARCHAR(2), CampusZip VARCHAR(10))

INSERT INTO @campus

SELECT
racac.CompanyID,
racac.CampusID,
ac2.CompanyName CampusName,
aca2.CompanyStreet CampusStreet,
aca2.CompanyCity CampusCity,
aca2.CompanyStateID CampusState,
'CampusZip' = CASE
WHEN aca2.CompanyZip4 IS NULL OR aca2.CompanyZip4 < 1
THEN aca2.CompanyZip
ELSE aca2.CompanyZip + '-' + CAST(aca2.CompanyZip4 AS CHAR(4))
END

FROM raCompanyaCampus AS racac
INNER JOIN aCompany AS ac
ON ac.CompanyID=racac.CompanyID
LEFT JOIN aCompany as ac2
ON ac2.companyid=racac.CampusID
LEFT JOIN aCompanyAddress aca2
ON racac.campusid=aca2.companyid
INNER JOIN tState ts
ON aca2.CompanyStateID = ts.StateID

ORDER BY racac.CompanyID

SELECT CompanyID,
MAX(CASE WHEN CampusNumber = 1 THEN CampusID ELSE NULL END) AS Campus1,
MAX(CASE WHEN CampusNumber = 2 THEN CampusID ELSE NULL END) AS Campus2,
MAX(CASE WHEN CampusNumber = 3 THEN CampusID ELSE NULL END) AS Campus3,
MAX(CASE WHEN CampusNumber = 4 THEN CampusID ELSE NULL END) AS Campus4
FROM
(
SELECT CompanyID, CampusID,
(SELECT COUNT(*) FROM @campus b WHERE b.CompanyID = a.CompanyID and b.CampusID <= a.CampusID) AS CampusNumber
FROM
@campus a
)
b
GROUP BY CompanyID

Thanks,
Jen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-19 : 16:12:06

it is the exact same thing. definitely spend some time to make sure all of this all makes complete sense before you implement it.

SELECT CompanyID,
MAX(CASE WHEN CampusNumber = 1 THEN CampusID ELSE NULL END) AS Campus1,
MAX(CASE when CampusNumber = 1 THEN CampusStreet Else Null END) as Campus1Street,
MAX(CASE WHEN CampusNumber = 1 THEN CampusCity ELSE Null END) as Campus1City,

...etc...

but now that you are adding more data, I have to ask: why do you want the results in this format?

- Jeff

itsmeeh
Starting Member

7 Posts

Posted - 2003-06-19 : 18:30:43

Hi Jeff. Thanks so much. It works perfectly and I am getting the result set that I want.

I'm creating this survey in crystal which includes all of the demographic information for a company and all of their campuses (including addresses). I needed to get the campus data in this format or else the companies would have been duplicated. This script is just a portion of a larger stored procedure that I have to figure out how to combine into one.

Anyways, you've been a tremendous help and i've learned quite a bit!

Thanks again!!!

Jen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-19 : 18:41:03

Consider creating a GROUP in crystal for Company, and in the Company group header put the company info.

then, in the detail section, you can list all of the campuses. that way, the Company is not repeated -- it is only printed once per group.

It is infinitely more flexible to let Crystal handle the formatting; then you can have as many campuses per company as you like, and you don't have to write these inefficient (and complicated!) cross-tab queries in SQL.

If you haven't used Groups in your reports before, read about them ! very handy and useful !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources