Merging multi results into one row

[Wouter Coinless]

ID Name Access1 Access2 Access3 Access4 Access5
918a6fb8-573f-4744-b4de-019bc8322ce4 Gast False False False False True
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc Administrator True True True True False

I have multiple results for 1 user. In the example I want the results to get to one row, with the final result (True, True, True, True, True). So the query has to go through all the results and check if the Access Right is True or False. Is one of them is True the result of the column is true. I tried to use Temp Tables but im stuck :( Anyone has an idea? I use MSSQL

[Luc Pattyn]

That doesn't look the way I think it should. I'd rather have a table with more rows and fewer columns, and because of normalization I'd suggest two tables, one with names, one with access rights:

ID Name
918a6fb8-573f-4744-b4de-019bc8322ce4 Gast
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc Administrator

ID AccessNumber AccessRight
918a6fb8-573f-4744-b4de-019bc8322ce4 1 False
918a6fb8-573f-4744-b4de-019bc8322ce4 2 False
918a6fb8-573f-4744-b4de-019bc8322ce4 3 False
918a6fb8-573f-4744-b4de-019bc8322ce4 4 False
918a6fb8-573f-4744-b4de-019bc8322ce4 5 True
etc.
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc 4 False

which would be much easier to query (you can join on ID; you can select on right=true, etc), as well as easier to extend when new access numbers become necessary. :)

Luc Pattyn [Forum Guidelines] [My Articles]

---

The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

---

[Lost User]

The MAX operator won't work on bits, but you can cast them to integers;

SELECT MAX(CAST(Access1 AS INT))
,MAX(CAST(Access2 AS INT))
,MAX(CAST(Access3 AS INT))
,MAX(CAST(Access4 AS INT))
,MAX(CAST(Access5 AS INT))
FROM dbo.TestTable

You'd have to specify a decent WHERE-clause, though. Another option would be the use of a function, that takes the ID as a parameter and which returns those five fields. It could then be used in a view :) -- Edit -- ..you should really consider Luc's post carefully, a simpeler design makes a lifetime of simple maintenance :cool:

"Every normal man must be tempted at times to spit on his hands, hoist the black flag, and begin to slit throats." -- H.L. Mencken, US editor (1880 - 1956)

[Wouter Coinless]

Yes i will consider that :) Havent thought about it that way.