Flatten File

I try to flatten a table.
in Access I would do this: iif(sequence=1,[Technology],Null) AS Tech1, iif(sequence=2,[Technology],Null) AS Tech2 FROM tblTechnologies GROUP BY Application
How do I flatten a file in SQL Server?
Thankslook up CASE in Books OnLine -- it does the same as the access IIF

rudy
http://rudy.ca/|||Great, Thanks. Step 1 completed.

Now I try to group the records so they all show up as 1 record with the primary key, but SQL want me to show list all fields i.e. GROUP BY itemID, sequence,Technology
In this case the fields will never show on one line.

Any suggestions?
Thanks..|||Hi Torgue

I just have a question. How can many records be grouped together and shown to all have the same Primary Key? If five records are grouped together (on sequence,Technology) each with there own Primary Key (itemID), which one of the five (itemID) do you choose to show when grouping?|||I'm sorry for the confusion. It is actually the Foreigh Key on the N-end of a 1:N relationship. The main table lists the applications and the N-side lists the technologies used for the application development.

In an Excel export I need to display the application with all technologies.

Like:
Application1 Tech1 Tech2 Tech2
Application2 Tech1 Tech2 Tech3

Thx..|||why don't you define the sql/server table in access as a linked table via odbc, and then write a crosstab query in access? i think you can easily export that to excel, too

in any case, if you do want to run this right in sql/server, you need
select application
, case when sequence=1
then technology else null end as tech1
, case when sequence=2
then technology else null end as tech2
from ... or something similar

rudy|||Thanks very much for your help.

Unfortunately I am using SQL Server as a web Backend, so I need to stick to SQL Server.

I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.|||I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.if you have already tried my CASE example, you will notice that each of the 1:N rows is present, and i apologize for not having shown how to summarize select application
, max( case when sequence=1
then technology else '' end ) as tech1
, max( case when sequence=2
then technology else '' end ) as tech2
from ...
group by applicationthe problem is, i don't know if you are doing any summing (is "technology" a char field?)

if you still cannot figure it out, please show your two table layouts

rudy|||works great, thank you very much.

technology is a character field, but the max takes the populated fields over the null fields.sql