SQL pivot table on text value

sql pivot explained
sql pivot string column
sql pivot without aggregate
pivot varchar column in sql server
sql pivot rows to columns dynamic
static pivot in sql server
sql pivot for all values
convert multiple rows to columns in sql

I am trying to pivot the following set of data in SQL.

SELECT  ParticipantID,
        ContactReasonTypeID,
        Completed = CASE WHEN Returned IS NOT NULL
                        THEN    'Completed'
                        ELSE 'Not completed'
                    END
FROM    ContactReason
WHERE   ContactReasonTypeID = 40 -- M4
UNION
SELECT  ParticipantID,
        ContactReasonTypeID,
        Completed = CASE WHEN Returned IS NOT NULL
                        THEN    'Completed'
                        ELSE 'Not completed'
                    END
FROM    ContactReason
WHERE   ContactReasonTypeID = 80 -- M8

Which gives the following result:

ParticipantID | QuestionnaireID  | Completed
1               40                 'Completed'
1               80                 'Not completed'
2               40                 'Completed'
2               80                 'Completed'
3               40                 'Completed'
3               80                 'Not completed'
4               40                 'Completed'
4               80                 'Not completed'

What I would like is for the results to be pivoted like so (where M4 is QuestionnaireID 40 and M8 is 80):

ParticipantID | M4           | M8
1               'Completed'    'Not completed'
2               'Completed'    'Completed'
3               'Completed'    'Not completed'
4               'Completed'    'Not completed'

I'm struggling on how to pivot the table. My current SQL is as follows and is resulting in errors. I've tried switching around the columns based on other posts but can't work out what is needed:

SELECT  *
FROM    (
    SELECT  ParticipantID,
            ContactReasonTypeID,
            Completed = CASE WHEN Returned IS NOT NULL
                            THEN    'Completed'
                            ELSE 'Not completed'
                        END
    FROM    ContactReason
    WHERE   ContactReasonTypeID = 40
    UNION
    SELECT  ParticipantID,
            ContactReasonTypeID,
            Completed = CASE WHEN Returned IS NOT NULL
                            THEN    'Completed'
                            ELSE 'Not completed'
                        END
    FROM    ContactReason
    WHERE   ContactReasonTypeID = 80
) AS tbl
PIVOT (
    MAX(Returned)
    FOR ContactReasonTypeID IN ([M4],[M8])
) AS pvt
ORDER BY ParticipantID

You can use the command PIVOT for this

DECLARE @t table(ParticipantID INT, QuestionnaireID INT, Completed varchar(20))
INSERT @t values
(1,40,'Completed'),(1,80,'Not completed'),(2,40,'Completed'),
(2,80,'Completed'),(3,40,'Completed'),(3,80,'Not completed'),
(4,40,'Completed'),(4,80,'Not completed')


SELECT ParticipantID, [40] M4, [80] M8
FROM @t
PIVOT
  (min(COMPLETED)  
FOR QuestionnaireID
  in([40],[80])  
)AS p
ORDER BY ParticipantID

Result:

ParticipantID   M4          M8
1               Completed   Not completed
2               Completed   Completed
3               Completed   Not completed
4               Completed   Not completed

SQL Server pivoting on non-numeric data types, Table 2: Pivot on Dates. The first step to pivoting on date fields is to convert your date field to an integer value. Figure 1 shows how we have  Usually you can only show numbers in a pivot table values area, even if you add a text field there. By default, Excel shows a count for text data, and a sum for numerical data. By default, Excel shows a count for text data, and a sum for numerical data.

You can try the following code:

SELECT  DISTINCT CR.ParticipantID,
        (SELECT CASE
                    WHEN Returned IS NOT NULL
                    THEN 'Completed'
                    ELSE 'Not completed'
                END M4
           FROM ContactReason
          WHERE ContactReasonTypeID = 40 --M4
            AND ParticipantID = CR.ParticipantID) M4,
        (SELECT CASE
                    WHEN Returned IS NOT NULL
                    THEN 'Completed'
                    ELSE 'Not completed'
                END M4
           FROM ContactReason
          WHERE ContactReasonTypeID = 80 --M8
            AND ParticipantID = CR.ParticipantID) M8
FROM    ContactReason CR
WHERE   CR.ContactReasonTypeID IN (40, 80) -- M4 and M8

How to Show Text in Pivot Table Values Area, I am trying to PIVOT a SQL table in which one of the fields is of type TEXT. Since none of I'm having trouble visualizing what you are trying to dowhat values Stack Overflow Public questions and answers; Pivot Table of Text. 0. Add a column with a default value to an existing table in SQL Server.

You may try this :

SELECT q.ParticipantID, 
                  CASE WHEN min(q.QuestionnaireID) = 40
                        THEN min(q.Completed)
                    END as M4,
                   CASE WHEN max(q.QuestionnaireID) = 80
                        THEN max(q.Completed)
                    END as M8
  FROM
(
SELECT  ParticipantID,
        ContactReasonTypeID,
        Completed = CASE WHEN Returned IS NOT NULL
                        THEN    'Completed'
                        ELSE 'Not completed'
                    END
FROM    ContactReason
WHERE   ContactReasonTypeID = 40 -- M4
UNION
SELECT  ParticipantID,
        ContactReasonTypeID,
        Completed = CASE WHEN Returned IS NOT NULL
                        THEN    'Completed'
                        ELSE 'Not completed'
                    END
FROM    ContactReason
WHERE   ContactReasonTypeID = 80
) q
GROUP BY ParticipantID;

Inside Demo, consider inner query q as tab2.

PIVOT rows that has a TEXT type field in SQL, UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. The syntax for PIVOT  are in your table and what would you like your pivot to display for those values? From: SajWilson via sql-server-l [mailto:sql-server-l@Groups.ITtoolbox.com] Sent: Tuesday, October 06, 2009 1:35 PM To: Johnson, Rose Subject: [sql-server-l] PIVOT rows that has a TEXT type field in SQL. Posted by SajWilson (Engineer) on Oct 6 at 4:36 PM . I am

Using PIVOT and UNPIVOT, Select one cell in your data set and press Ctrl+T (or go to Home, Format as Table​). 2. The result is a pivot table with text in the Values area. The PIVOT Keyword in Oracle SQL. Back to Top. Oracle has the ability to create a result set that transposes or pivots columns and rows to provide a summary. This is done using the SQL PIVOT keyword. This keyword was introduced in Oracle 11g. This keyword is applied to a SELECT statement, and looks like this:

Excel: Reporting Text in a Pivot Table, For example, if the column "ITEM_HEAD_ID" values can only be like 1, @​ColumnsTable TABLE ([ColumnName] VARCHAR(50)); INSERT  Start building the pivot table To add the text to the values area, you have to create a new special kind of calculated field called a Measure. Look at the top of the Pivot Table Fields list for the table name. Right-click the table name and choose Add Measure.

How to create a pivot query in sql server without aggregate function , To add a Measure, select the pivot table ➜ right click on the table of data pivot tables just don't have, including concatenating text values to  First you see that we’re constructing a SQL statement in a VARCHAR. Also, notice that the column names, calculated in step two and housed in @Columns, are placed in the statement as well. When complete the variable @SQL will contain the complete pivot statement. It is the static pivot statement intern wrote.

Comments
  • This approach worked well, however I marked the selected answer due to the use of pivot table.