CODEWITHSUNDEEP
sqlsql-server
zuiqo
zuiqo

Reputation: 1199

Generate a result that contains multiple values from one column by WHERE

I'm inexperienced with SQL, so my wording may be totally wrong. If someone could rephrase my question, I assume it'll be easy to google. Using Ms-SQL Server 2008.

I have a table where I log imports with the following columns:

importId
date
filename

In my other tables, I import three different tables per day. These can be identified by filename (such as contracts_20170105.csv, accounts_20170105.csv, ...), and I have exactly one file of each type per day.

My desired result looks like this:

date importId_contracts importId_accounts

I can identify each one individually by using

SELECT
    importId
FROM
    imports
WHERE
    CONVERT(DATE, date) = '2017-01-05' AND
    SUBSTRING(import.filename, 1, 5) = 'Accou'

but I don't know how to combine the result into the aforementioned form.

How would this be called in SQL 'slang'?

Thank you!

Upvotes: 1

Views: 66

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This will work if you have only 1 file per type each day:

select date, 
       max(case when filename like 'contracts%' then importId end) as importId_contracts
       max(case when filename like 'accounts%' then importId end) as importId_accounts 
from Imports
where date = '2017-01-05'
group by date

Upvotes: 5

Related Questions