Get multiple records from column into one row .

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)

Get multiple records from column into one row .

Author

Topic

arnie275
Starting Member

7 Posts

Posted - 2004-03-17 : 19:22:42

Hello,

I am trying to select multiple records from one table into a single row. It is for a student database and students can be in multiple programs. The State wants to know some of these programs when it comes to testing time.

sample table data


Name   Program
Joe    GATE
Joe    ESEAT
Tom    GATE
Jill   GATE

I want the record to look something like this.


Name  Migrant Education   Indian Education   GATE   ESEA Title-1
Joe                                          Yes    Yes
Jill                                         Yes
Tom                                          Yes

Which is no problem but the data is not being pulled correctly. Joe does not get both GATE and ESEA only one.
Currently I am using temp tables to pull data from other tables. Below is the code I am currently using to populate the fields I am having problems with. It only pulls one record for a student. I need check for all and put 'Yes' if they are in the program.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

update testing.dbo.preidSTARmc 

	set  ME = CASE --Migrant Education
		WHEN p.CD = '135' THEN 'Yes'
		ELSE ''
		END,
	     IE = CASE --Indian Education
		WHEN p.CD = '132' THEN 'Yes'
		ELSE ''
		END,
	   GATE = CASE --Gifted and Talented
		WHEN p.CD = '127' THEN 'Yes'
		ELSE ''
		END,
	  ESEAT = CASE --ESEA Title 1 - Targeted
		WHEN p.SC IN (780,940)
		THEN CASE
		WHEN p.CD = '122' THEN 'Yes'
		ELSE ''
		END	
		ELSE ''
		END

from #Tpgm p (nolock) 

join #Tstu s (nolock) on  p.SC = s.SC AND p.SN = s.SN  

join testing.dbo.preidSTARmc preidSTARmc (nolock) on s.id = preidSTARmc.id

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Any suggestions or help would be appreciated,
Arnie275

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 14:19:01

Perhaps you would get a reply if you posted the DDL and DML for sample data. DDL would be CREATE TABLE statement for all tables involved. DML would be INSERT INTO statements for sample data. We would also need to see the expected result set using the sample data.

You'll find your question gets answered quickly if we are given all of the information. Otherwise, your question may go unanswered as we have to do all of that typing.

Tara

arnie275
Starting Member

7 Posts

Posted - 2004-03-18 : 14:41:06

quote:
Originally posted by tduggan

Perhaps you would get a reply if you posted the DDL and DML for sample data. DDL would be CREATE TABLE statement for all tables involved. DML would be INSERT INTO statements for sample data. We would also need to see the expected result set using the sample data.

You'll find your question gets answered quickly if we are given all of the information. Otherwise, your question may go unanswered as we have to do all of that typing.

Tara

Sorry this is my first time posting and I am fairly new at getting this deep into SQL, hopefully this will help.
I changed the code only where table names were referenced to match the tables created.


CREATE TABLE preidSTARmc  (
SN int NOT NULL,
ID int,
NM varchar(30),
SC int,
ME varchar(3),
IE varchar(3),
GATE varchar(3),
ESEAT varchar(3)
)

CREATE TABLE pgm(
SN int NOT NULL,
SC int
CD int
)

CREATE TABLE stu (
SN int NOT NULL,
ID int,
NM varchar(30),
SC int
)

Insert into pgm values(1,100,132)
Insert into pgm values(2,780,127)
Insert into pgm values(2,780,122)
Insert into pgm values(3,940,122)
Insert into pgm values(3,940,132)
Insert into pgm values(4,100,135)
Insert into pgm values(4,100,127)
Insert into pgm values(5,100,127)


Insert into stu values(1,10,'joe',100)
Insert into stu values(2,20,'jill',780)
Insert into stu values(3,30,'tom',940)
Insert into stu values(4,40,'chris',100)
Insert into stu values(5,50,'sandra',100)

Insert into preidSTARmc values(1,10,'joe',100,'','','','')
Insert into preidSTARmc values(2,20,'jill',780,'','','','')
Insert into preidSTARmc values(3,30,'tom',940,'','','','')
Insert into preidSTARmc values(4,40,'chris',100,'','','','')
Insert into preidSTARmc values(5,50,'sandra',100,'','','','')

update preidSTARmc 

	set  ME = CASE --Migrant Education
		WHEN p.CD = '135' THEN 'Yes'
		ELSE ''
		END,
	     IE = CASE --Indian Education
		WHEN p.CD = '132' THEN 'Yes'
		ELSE ''
		END,
	   GATE = CASE --Gifted and Talented
		WHEN p.CD = '127' THEN 'Yes'
		ELSE ''
		END,
	  ESEAT = CASE --ESEA Title 1 - Targeted
		WHEN p.SC IN (780,940)
		THEN CASE
		WHEN p.CD = '122' THEN 'Yes'
		ELSE ''
		END	
		ELSE ''
		END

from pgm p (nolock) 

join stu s (nolock) on  p.SC = s.SC AND p.SN = s.SN  

join preidSTARmc pre (nolock) on s.id = pre.id

---expected results------ 

SN   ID  NM       SC   ME   IE   GATE  ESEAT 
1    10  joe      100       Yes
2    20  jill     780            Yes   Yes
3    30  tom      940       Yes        Yes
4    40  chris    100  Yes       Yes
5    50  sandra   100            Yes

Thanks,
Arnie275

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 16:57:57

Your UPDATE statement doesn't reflect the DDL that you provided. This part:

join preidSTARmc pre (nolock) on s.id = pre.id

is blowing up because id doesn't exist in the s or pre table. Does id just need to be added to these two tables or does another column already represent this?

Tara

arnie275
Starting Member

7 Posts

Posted - 2004-03-18 : 17:02:21

quote:
Originally posted by tduggan

Your UPDATE statement doesn't reflect the DDL that you provided. This part:

join preidSTARmc pre (nolock) on s.id = pre.id

is blowing up because id doesn't exist in the s or pre table. Does id just need to be added to these two tables or does another column already represent this?

Tara

I apologize for that, id just needs to be added. I have edited the post so it is now included.

Arnie

arnie275
Starting Member

7 Posts

Posted - 2004-03-18 : 17:09:19

quote:

Originally posted by arnie275

quote:
Originally posted by tduggan

Perhaps you would get a reply if you posted the DDL and DML for sample data. DDL would be CREATE TABLE statement for all tables involved. DML would be INSERT INTO statements for sample data. We would also need to see the expected result set using the sample data.

You'll find your question gets answered quickly if we are given all of the information. Otherwise, your question may go unanswered as we have to do all of that typing.

Tara

Sorry this is my first time posting and I am fairly new at getting this deep into SQL, hopefully this will help.
I changed the code only where table names were referenced to match the tables created.


CREATE TABLE preidSTARmc  (
SN int NOT NULL,
ID int,
NM varchar(30),
SC int,
ME varchar(3),
IE varchar(3),
GATE varchar(3),
ESEAT varchar(3)
)

CREATE TABLE pgm(
SN int NOT NULL,
SC int,
CD int
)

CREATE TABLE stu (
SN int NOT NULL,
ID int,
NM varchar(30),
SC int
)

Insert into pgm values(1,100,132)
Insert into pgm values(2,780,127)
Insert into pgm values(2,780,122)
Insert into pgm values(3,940,122)
Insert into pgm values(3,940,132)
Insert into pgm values(4,100,135)
Insert into pgm values(4,100,127)
Insert into pgm values(5,100,127)


Insert into stu values(1,10,'joe',100)
Insert into stu values(2,20,'jill',780)
Insert into stu values(3,30,'tom',940)
Insert into stu values(4,40,'chris',100)
Insert into stu values(5,50,'sandra',100)

Insert into preidSTARmc values(1,10,'joe',100,'','','','')
Insert into preidSTARmc values(2,20,'jill',780,'','','','')
Insert into preidSTARmc values(3,30,'tom',940,'','','','')
Insert into preidSTARmc values(4,40,'chris',100,'','','','')
Insert into preidSTARmc values(5,50,'sandra',100,'','','','')

update preidSTARmc 

	set  ME = CASE --Migrant Education
		WHEN p.CD = '135' THEN 'Yes'
		ELSE ''
		END,
	     IE = CASE --Indian Education
		WHEN p.CD = '132' THEN 'Yes'
		ELSE ''
		END,
	   GATE = CASE --Gifted and Talented
		WHEN p.CD = '127' THEN 'Yes'
		ELSE ''
		END,
	  ESEAT = CASE --ESEA Title 1 - Targeted
		WHEN p.SC IN (780,940)
		THEN CASE
		WHEN p.CD = '122' THEN 'Yes'
		ELSE ''
		END	
		ELSE ''
		END

from pgm p (nolock) 

join stu s (nolock) on  p.SC = s.SC AND p.SN = s.SN  

join preidSTARmc pre (nolock) on s.id = pre.id

---expected results------ 

SN   ID  NM       SC   ME   IE   GATE  ESEAT 
1    10  joe      100       Yes
2    20  jill     780            Yes   Yes
3    30  tom      940       Yes        Yes
4    40  chris    100  Yes       Yes
5    50  sandra   100            Yes

Thanks,
Arnie275

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 17:13:30

Do you need preidSTARmc?



select 	s.sn, id, s.nm, s.sc, 
	me = 
	  CASE --Migrant Education
		WHEN p.CD = '135' THEN 'Yes'
		ELSE ''
	  END,
	IE = 
	  CASE --Indian Education
		WHEN p.CD = '132' THEN 'Yes'
		ELSE ''
	  END,
	GATE = 
	  CASE --Gifted and Talented
		WHEN p.CD = '127' THEN 'Yes'
		ELSE ''
	  END,
	ESEAT = 
	  CASE --ESEA Title 1 - Targeted
		WHEN p.SC IN (780,940) THEN 
					  CASE
						WHEN p.CD = '122' THEN 'Yes'
						ELSE ''
					  END	
		ELSE ''
	  END
from stu s
inner join pgm p on p.SC = s.SC AND p.SN = s.SN

I realize the rows aren't grouped, but I don't want to go any further if you need that other table. It just seems to me you should SELECT the data you want not UPDATE a table that you don't need since you can get to the data.

Tara

arnie275
Starting Member

7 Posts

Posted - 2004-03-18 : 17:26:28

The way I am currently doing things I do need the preidSTARmc table. The code I have posted it just one part of a larger program that pulls data from about 5 other tables. The reason I am trying to get it all on one line is because I have created and MS Access Form the record for a specific student, so by having it all on one row I can find a specific student and display all the information about them. I did this because the people using the information a very non-technical and need a very simple way to get at data. I tried doing a Select from all the tables at once but was not able to get rid of duplications.

arnie275
Starting Member

7 Posts

Posted - 2004-03-18 : 17:43:12

Just realized this,

If there is a way to do it without UPDATE I think that would fix the duplication I got by combining all the tables in one select. So in that case I wouldn't need the preidSTARmc table. But I still need the records to be one row per person.

Thanks,

Arnie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 17:46:50

Yeah, my SELECT works except it doesn't GROUP them together for one person. Working on that now. Having lots of problems.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 17:57:57

Ok, here ya go:



SELECT	s.sn, s.id, s.nm, s.sc, 
	MAX(ME) AS ME, MAX(IE) AS IE, MAX(GATE) AS GATE, MAX(ESEAT) AS ESEAT
FROM 	stu s
INNER JOIN 
	(SELECT	s.sn, s.id, s.nm, s.sc, 
		ME = 
	  	  CASE --Migrant Education
			WHEN p.CD = '135' THEN 'Yes'
			ELSE ''
	  	  END,
		IE = 
	  	  CASE --Indian Education
			WHEN p.CD = '132' THEN 'Yes'
			ELSE ''
	  	  END,
		GATE = 
	  	  CASE --Gifted and Talented
			WHEN p.CD = '127' THEN 'Yes'
			ELSE ''
	  	  END,
		ESEAT = 
	  	  CASE --ESEA Title 1 - Targeted
			WHEN p.SC IN (780,940) THEN 
					 	 CASE
							WHEN p.CD = '122' THEN 'Yes'
						 	ELSE ''
					 	 END	
		  	ELSE ''
	  	  END
	FROM stu s
	INNER JOIN pgm p ON p.SC = s.SC AND p.SN = s.SN
	) t
ON 	t.SC = s.SC AND t.SN = s.SN
INNER JOIN pgm p ON p.SC = s.SC AND p.SN = s.SN
GROUP BY s.sn, s.id, s.nm, s.sc

My original SELECT is the joined derived table. I join to it so that I can get the non-grouped by columns.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-18 : 18:05:03

Tara's great query can be slightly compressed (I Think)...


SELECT s.sn, id, s.nm, s.sc, 
me =    CASE WHEN '135' IN (MAX(p.CD),MIN(p.CD))  THEN 'Yes' ELSE ''   END,
IE =   CASE WHEN '132' IN (MAX(p.CD),MIN(p.CD)) THEN 'Yes' ELSE ''   END,
GATE =    CASE WHEN '127' IN (MAX(p.CD),MIN(p.CD)) THEN 'Yes' ELSE ''   END,
ESEAT =   CASE WHEN MAX(p.SC) IN (780,940) OR MIN(p.sc) IN (780,940) THEN 
		  CASE WHEN '122' IN (MAX(p.CD),MIN(p.CD)) THEN 'Yes'	ELSE ''   END
		ELSE ''		 END
from stu s
inner join pgm p on p.SC = s.SC AND p.SN = s.SN
group by s.sn, id, s.nm, s.sc

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 18:07:34

Yeah great meaning large. And yes it can. Your result set is the same as mine. Great work.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-18 : 18:09:32

But will it work for other data sets???????

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

arnie275
Starting Member

7 Posts

Posted - 2004-03-18 : 18:20:18

Thanks so much this should work for me. I just need to get it into the rest of my program.

Thanks again,
Arnie275

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources