How can I select a bool column from a table into a VARCHAR?

Question

I have the following table:

CREATE TABLE [dbo].[Answer] (
    [AnswerId]    INT              IDENTITY (1, 1) NOT NULL,
    [QuestionId]  INT              NOT NULL,
    [Correct]     BIT              NULL
);

Here is some typical data:

AnswerId   QuestionId   Correct
22         9            0
23         9            0
24         9            1

I need to get the answer 001

AnswerId   QuestionId   Correct
22         9            1
23         9            0
24         9            1

I need to get the answer 101

SELECT QUERY construct to filter questionid 9 and orderby Answerid is u need with the generated column value '001'. Am i right? — knkarthick24, Jul 23 '14 at 10:29
It seems to me that a Concatenate aggregate function is needed. The following questions might help: [Concatenate row values T-SQL](http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql), [Custom aggregate function (concat) in SQL Server](http://stackoverflow.com/questions/4374709/custom-aggregate-function-concat-in-sql-server). — Greenstone Walker, Jul 23 '14 at 10:41

Pred · Accepted Answer · 2014-07-23T10:33:56.593

If I understand you correctly, you have to list all values in the Correct column for a specific question in the order of the AnswerId in one concatenated string.

Here is a generic solution for a specific QuestionId (Recursive CTE)

DECLARE @Answers TABLE (
    [AnswerId]    INT              NOT NULL,
    [QuestionId]  INT              NOT NULL,
    [Correct]     BIT              NULL
);

INSERT INTO @Answers (AnswerId,QuestionId,Correct) VALUES
(22,         9,            0),
(23,         9,            0),
(24,         9,            1);


;WITH Partitioned AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY QuestionId ORDER BY AnswerId ASC) AS RowNumber
    , COUNT(1) OVER (PARTITION BY QuestionId) AS ColumnCount
    , CONVERT(VARCHAR(MAX), Correct) AS Correct
  FROM
    @Answers
  WHERE
    [QuestionId] = 9
),
Concatenated AS (
  SELECT RowNumber, ColumnCount, Correct FROM Partitioned WHERE RowNumber = 1

  UNION ALL

  SELECT
      P.RowNumber
      , P.ColumnCount
      , C.Correct + P.Correct AS Correct
  FROM
    Partitioned P
    INNER JOIN Concatenated C
        ON P.RowNumber = C.RowNumber + 1
)
SELECT
    CONVERT(VARCHAR(20), Correct) AS Correct
FROM
    Concatenated
WHERE
    RowNumber = ColumnCount

Note: Change @Answers to your table's name and remove the table declaration and inserts.

Another solution is to write a CLR function to concatenate the values in the Correct column.

This answer does work but I am not sure I understand how it works. The answer table will have a maximum of just ten rows so I am wondering if there is a simpler way to do this. I will wait to see if anyone else can come up with a simpler way. — , Jul 24 '14 at 03:07
This is a [`Recursive CTE`](https://www.google.hu/webhp#q=t-sql+recursive+cte) (You can read more about them by yourself) to `Concatenate` values. (There are more solutions to do this: with [XML](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) functions and with a [CLR](https://www.google.hu/webhp#q=sql+clr+group_concat)). CLR could be the fastest, but requires special options and it is not a pure SQL solution. — Pred, Jul 31 '14 at 11:52

mehdi lotfi · Answer 2 · 2014-07-23T10:26:21.113

-1

Try this:

SELECT *,
       '00'+CASE WHEN correct = 1 THEN '1' ELSE '0' END 
FROM dbo.Answer
WHERE questionId = 9

EDIT: In order to save result on the variable use following format. (save only one record on the variable)

Declare @YourVariable Nvarchar(20)

SELECT @YourVariable = '00'+CASE WHEN correct = 1 THEN '1' ELSE '0' END 
FROM dbo.Answer
WHERE questionId = 9

edited Jul 23 '14 at 10:26

answered Jul 23 '14 at 10:19

mehdi lotfi

11,194
18
82
128

How could I put the value into a variable I declare? – Jul 23 '14 at 10:25
1

What happens, if the row with `AnswerId = 22` contains `1` in the `Correct` column – Pred Jul 23 '14 at 10:26
I edit my post in order to use variable. but you can save only one record on variable. – mehdi lotfi Jul 23 '14 at 10:26
@Pred - This answer would not work. When I mentioned I needed the data to look like 001 I was not really thinking that an answer would include "00" first. What I need is a horizontal view of the data in the correct column. – Jul 23 '14 at 19:53

score -1 · Answer 3 · answered Jul 23 '14 at 10:31

-1

A straightforward way would be: Convert bit to varchar glue "00" to the beginning convert the whole to varchar(20)

Like:

SELECT  Convert(VARCHAR(20),(CONVERT(VARCHAR(2),N'00') + CONVERT(VARCHAR(1),Correct))) as converted from Answer where (QuestionId=9) order by AnswerId;

answered Jul 23 '14 at 10:31

user2316338

9
1

What happens, if the row with `AnswerId = 22` contains `1` in the `Correct` column? (Once again :) ) – Pred Jul 23 '14 at 10:41
@Pred Then the response of my query will be VARCHAR(20) with "001" in it, exactly as the OP asked for. Am I missing anything here? – user2316338 Jul 23 '14 at 12:37
If the OP wants exactly the `001` string, then the concatenation is unnecessary. On the other hand, if the OP wants to list all values from the `Correct` field concatenated together in the order of `AnswerId`, then this solution simply will not work in most cases. – Pred Jul 23 '14 at 12:42
@Pred The OP DID want result of datatype VARCHAR(20). (He didn't tell why, but you SHOULD convert your result to VARCHAR(20) in that case.) Futhermore, the OP DID NOT ask for any kind of concatenated fields. He asked for a way to display his BIT datatype as a VARCHAR(20), with padding zero's. – user2316338 Jul 23 '14 at 14:08
Please read the question once more. The OP never asked to cast the BIT datatype to VARCHAR(20), but, let the OP decide, what he wanted. – Pred Jul 23 '14 at 14:11
I updated the question to make it a bit more clear. I am sorry if it was confusing but I don't need "00" at the start of each answer. Thanks – Jul 23 '14 at 19:54

How can I select a bool column from a table into a VARCHAR?

3 Answers3