Moving Column Cell Values into a Row as Columns

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)

Moving Column Cell Values into a Row as Columns

Author

Topic

ericrsteele
Starting Member

1 Post

Posted - 2010-07-30 : 13:27:37

Hi all,

I am working with the following tables:

dbo.RcvSupplierTable

dbo.RcvFIPSTable

dbo.RcvMRTrailerLots

I need to make a SELECT statement that will give me the following:

- 'SupplierID' and 'Supplier' come from dbo.RcvSupplierTable.
- SupplierID's are DISTINCT
- There needs to be a 'FIPS#' column for each 'FIPSID' value in dbo.RcvFIPSTable WHERE 'SupplierID' matches the S'upplierID' in dbo.RcvFIPSTable.
- 'Total' is the SUM of the 'ProducerQty1' values that have matching SupplierID's in the RcvTrailerLots Table.

Here is where I am (and I may be way off)

SELECT DISTINCT s.SupplierID as 'Supplier ID', s.Supplier as 'Description', f.FIPSID as 'FIPS ID', SUM(r.ProducerQty1)

FROM RcvSupplierTable s
JOIN RcvFIPSTable f ON f.SupplierID = s.SupplierID
JOIN RcvMRTrailerLots r ON s.SupplierID = CAST( LEFT(r.SupplierID, CHARINDEX('-',r.SupplierID)-1) AS INT)
GROUP BY s.SupplierID, s.Supplier, f.FIPSID

The CAST and LEFT are used because the SupplierID's in (r) have extra text concatenated at the end that must be removed to compare it with s.SupplierID

I appreciate any guidance you can give! Thanks.

Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources