Flattening table results - 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 2005 Forums

Transact-SQL (2005)

Flattening table results

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-23 : 12:59:36

I have 2 tables that i am joining in a query, and i would like the results to be flattened. For example, the tables could be: t_Class and t_Student. A class could have any number of students. i want the results returned like this:

ClassId, Student1Id, Student2Id, Student3Id, etc...

anyone have an idea? i know i can do this with a ridiculous series of select statements, but i'm wondering if there's a more efficient way of doing this.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-23 : 13:10:25

Take a look at the PIVOT clause of the SELECT statement.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-23 : 20:45:14

I'm not entirely sure pivot is the solution for me. Here's more specifically what I want to do... Say I have a product, and this product has any number of shipment statuses. i want to return a row with the following:

[product columns], Available, Freight
[product data], YES, PAID

My exact code snippet is below... this isn't working quite right yet.



select	case	ShipmentStatusCode
			when  'AV' then 'YES'
			when 'DN' then 'NO' 
			else null
		end as Available,
		case	ShipmentStatusCode
			when 'CR' then 'PAID'
			when 'FD' then 'DUE'
		end as Freight
from (
	select	ShipmentStatusCode
	from	dbo.EDIShipmentStatuses SS
			inner join dbo.EDIContainers C on C.ContainerCode = SS.ContainerId
			inner join dbo.EDIContainerInstances CI on CI.ContainerId = C.ContainerId
	where	C.ContainerId = 1
) as PSS

FYI, this wasn't my ideal design. this is for a database at my work.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-23 : 20:46:11

the reason i suspect that this isn't the solution is because PIVOT uses aggregate functions. i need to translate codes into something readable and have it all on one row.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-23 : 20:50:41

also, in my code, pretend references to "Containers" are "Products" as i used in my example. accidentally pasted my EXACT code... i'm actually workign with shipping containers, not products.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 00:50:31

If you do not know the number of columns, take a look at this article,
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources