Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transforming Data in a Query 1

Status
Not open for further replies.

xlbo

MIS
Mar 26, 2002
15,080
GB
Hello all,
not great on SQL but I can get by - running into a bit of an issue that I can't seem to get round....

Using MS Reporting Services over a SQL Server database

Data is currently held like:

[tt]
AGM ID | Product Type | Tied Flag
1234 | Ales | Y
1234 | Lager | Y
1234 | Cider | Y
5678 | Ales | Y
5678 | Lager | Y
5678 | Cider | N
[/tt]

What I would like to know is if there is a standard methodology to transform that into:
[tt]
AGM ID | Ales tie | Lager Tie | Cider Tie
1234 | Y | Y | Y
5678 | Y | Y | N
[/tt]

Basically, doing the same as a "Crosstab Query" in MS Access.....

Any help would be appreciated

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Code:
select AGMID 
     , max(case when ProductType = 'Ales'
                then TiedFlag
             end) as AlesTie
     , max(case when ProductType = 'Lager' 
                then TiedFlag
             end) as LagerTie
     , max(case when ProductType = 'Cider' 
                then TiedFlag
             end) as CiderTie
  from yourtable
group 
    by AGMID
btw, what's Reporting Services? does it support regular SQL?

r937.com | rudy.ca
 
Cheers r937 - I will give that a go

Reporting Services is a BI tool from Microsoft which is being bundled with SQL Server. AFAIK, it supports regular (MS) SQL


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Thank you r937 - That seems to work nicely - have a star

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top