In SQL Server 2022 (v15), I have this table:
Table file
-------------
Id name Md5
1 abc.txt xxxxx1
2 bbb.doc xxxxx2
3 ccc.bin xxxxx3
If the client passes some md5s to query if these files in the database, I can make a query like,
SELECT Md5
FROM file
WHERE md5 IN ('xxx1', 'xxx2', ...)
This query returns a dataset with the existing file Md5s. The client needs to write some extra code to know whether xxx1 exists, whether xxx2 exists ...
Can I give the result for each file in the query? Like,
SELECT
CASE WHEN 'xxx1' IN ('xxx1', 'xxx2', ...) THEN 1 ELSE 0 AS 'xxx1',
CASE WHEN 'xxx2' IN ('xxx1', 'xxx2', ...) THEN 1 ELSE 0 AS 'xxx2',
...
FROM
....
WHERE
Md5 IN ('xxx1', 'xxx2', ...)
2条答案
按热度按时间64jmpszr1#
You could do a simple
PIVOT
Note: The
WHERE
is optional, but may be more performantExample
Results
pengsaosao2#
The easiest way to do this is to start with a table of the MD5 hashes to check, and left-join the
file
table.You can use a
VALUES
constructorOr, depending on how you pass the data in, you can use a table variable, temp table or Table-Valued Parameter.
The results would be something like this