How to Pivot Text Columns in SQL Server

How to pivot text columns in SQL Server?

Select severity_id, pt.People, Assets, Environment, Reputation
FROM 
(
    select * from COMM.Consequence
) As Temp
PIVOT
(
    max([DESCRIPTION]) 
    FOR [TYPE] In([People], [Assets], [Environment], [Reputation])
) As pt

Pivoting Text Values in SQL Server as 1 & 0

You can use what you already have, and do a case statement for each pivoted column, or you can use count as the aggregate function and to ensure the result doesn't exceed 1 by using distinct:

SELECT EMPID, EMPNAME, ACTION_DATE, HIRED, TERMINATED, REHIRED 
-- Make sure each row is distinct:
FROM (SELECT DISTINCT EMPID, EMPNAME, ACTION_DATE, ACTION_TYPE FROM EMPLOYEES) emps 
PIVOT (COUNT(ACTION_TYPE) -- At most there can be one row so count can only be 0 or 1.
  FOR ACTION_TYPE IN ([HIRED],[TERMINATED],[REHIRED])) AS P1

I'm not quite sure what you want to do with that ACTION_DATE, as it seems in your example you consolidate everything into one row. In the example below I just use MIN, which doesn't quite match what you're going for, but should be enough guidance to achieve what you want:

SELECT EMPID, EMPNAME, MIN(ACTION_DATE), SUM(HIRED), SUM(TERMINATED), SUM(REHIRED)
FROM (SELECT DISTINCT * FROM EMPLOYEES) emps
PIVOT (COUNT(ACTION_TYPE) 
  FOR ACTION_TYPE IN ([HIRED],[TERMINATED],[REHIRED])) AS P1 
GROUP BY EMPID, EMPNAME

Pivot SQL table with text rows

try this :

create table #temp (code int, languageID int, text varchar(10))
    insert into #temp values 
    (1,1,'Text1'),
   (1,2,'Text2'), 
   (1,3,'Text3'), 
   (1,4,'Text4'), 
   (2,1,'Text5'), 
   (2,2,'Text6'), 
   (2,3,'Text7'), 
   (2,4,'Text8')




select CODE, [1] As TextA, [2] As TextB, [3] As TextC, [4] As TextD
from 
(
  select CODE, LanguageID , Text
  from #temp
) src
pivot
(
  max(Text)
  for LanguageID  in ([1], [2], [3], [4])
) piv;

op:

CODE        TextA      TextB      TextC      TextD
----------- ---------- ---------- ---------- ----------
1           Text1      Text2      Text3      Text4
2           Text5      Text6      Text7      Text8

SQL Server pivot text values

If you want all rows, simply add a unique key like row_number()

Example

SELECT [Doctor] Doctor, [Singer] Singer, [Actor] Actor
FROM (
       Select *
             ,rn=row_number() over(order by ename) 
        from @occupations
     ) src
PIVOT
  (min(ename)  
    FOR occupation IN ([Doctor],[Singer], [Actor])  
  )AS pp

Returns

Doctor  Singer  Actor
Anna    NULL    NULL
NULL    NULL    Helen
Jack    NULL    NULL
NULL    Jim     NULL
John    NULL    NULL
NULL    Kate    NULL
Mary    NULL    NULL
NULL    Paco    NULL

Pivoting a Table with Text and no Aggregation

Just because you have text data in your table does not mean that you cannot use an aggregate function on it.

You did not specify what RDBMS you are using but this type of data transformation is a pivot. This converts row data into columns. Some databases has a pivot function but if you are using one without that function, then you will need to use an aggregate function with a CASE expression:

select lineid,
  max(case when question ='Height' then answer else '' end) Height,
  max(case when question ='Outside Color' then answer else '' end) [Outside Color]
from yourtable 
group by lineid

See SQL Fiddle with Demo

If you have a database that has the pivot function (SQL Server 2005+/Oracle 11g+), then your code will be similar to this:

select *
from 
(
  select lineid,
    Question,
    Answer
  from yourtable
) src
pivot
(
  max(answer)
  for question in ([Height], [Outside Color])
) piv;

See SQL Fiddle with Demo

Now if you are using SQL Server 2005+ and you have an unknown number of questions that you want to turn into columns, then you can use dynamic sql similar to this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Question) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT lineid,' + @cols + ' from 
             (
                select lineid,
                  Question,
                  Answer
                from yourtable
            ) x
            pivot 
            (
                max(Answer)
                for Question in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with demo

Based on your sample data the result is:

| LINEID |  HEIGHT | OUTSIDE COLOR |
------------------------------------
|      1 | 180 3/4 |    Dark Green |

Why is the Max function used when we pivot text columns in SQL Server?

Because in your example you've chosen EVENT as the value to show in the PIVOT intersections (i.e. since you've specified EVENT in the PIVOT clause), the value must be specified with one of the permissible aggregate functions, as there are potentially multiple rows for each of the column values that you've chosen in your pivot, when grouped by the remaining columns (i.e. DATE in your case).

In Sql Server^[1], MAX() or MIN() is commonly used when pivoting non-numeric columns, as it is able to show one of the original of the values of the column.

Any non-aggregate and non-pivoted columns will be left as-is and will be used to form the groups on which the pivot is based (in your case, column DATE isn't either in the aggregate, or the column pivot, so it will form the row group)

Consider the case where your pivoted table contains multiple rows matching your predicate, such as this:

INSERT INTO strategy (DATE, SITA, EVENT) VALUES
('1 Jan 2018', 'ABZPD', 'Event1'),
('1 Jan 2018', 'BFSPD', 'Event2'),
('1 Jan 2018', 'BFSPD', 'Event3');

After Pivot:

DATE                    ABZPD   BFSPD
2018-01-01T00:00:00Z    Event1  Event3

i.e. During the Pivot, the BFSPD rows for Event2 and Event3 needed to somehow be projected into a single cell - hence the need for an aggregate. This aggregate is still needed, even if there is known to be just one value (this being the case for the Event1 value for SITA ABZPD in the above example).

Since BFSPD has two events, you'll need to somehow resolve how to project a value into a single cell value. The use of MAX on the VARCHAR column resolves the 'largest' value (Event3) in the event of multiple rows in projecting into the same resulting pivot 'cell' - SqlFiddle example here

You could choose to use COUNT(Event) to show you the number of events per row / pivot intersection - Fiddle

And you could switch the aggregate on EVENT with DATE - EVENT is thus used in the column grouping.

_{*1 Aggregates like AVG or STDEV are obviously not available to strings. Other RDBMS have additional aggregates like FIRST which will arbitrarily take the first value, or GROUP_CONCAT / LIST_AGG, which can fold string values together with a delimiter. And PostGres allows you to make your own aggregate functions!. But sadly, none of this in SqlServer, hence MIN()
/ MAX() for now.}

SQL Pivot for text columns

If you know in advance the maximum number of names per id, you can enumerate them with row_number(), then pivot with conditional aggregation:

select t1.id, t1.name
    max(case when t2.rn = 1 then t2.name end) person_1,
    max(case when t2.rn = 2 then t2.name end) person_2,
    max(case when t2.rn = 3 then t2.name end) person_3,
    ...
from t1
inner join (
    select t2.*, row_number() over(partition by id order by name) rn
    from t2
) t2 on t2.id = t1.id
group by t1.id, t1.name

How can i sql pivot column

This isn't a pivot at all, it's string aggregation.

In SQL Server 2017+ or Azure SQL Database:

SELECT Attendee, Semester, Subjects = STRING_AGG(Subject, ',')
  FROM dbo.SomeTableName
  GROUP BY Attendee, Semester;

On older and unsupported versions, it's a heck of a lot uglier, not to mention inefficient:

SELECT ost.Attendee, ost.Semester, Subjects = STUFF((
  SELECT ',' + ist.Subject
    FROM dbo.SomeTableName AS ist
    WHERE ist.Attendee = ost.Attendee
      AND ist.Semester = ost.Semester
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '')
FROM dbo.SomeTableName AS ost
GROUP BY ost.Attendee, ost.Semester;

Example db<>fiddle

SQL Server 2008 pivot text column too large

This is a way to do a pivot without using the Pivot operator, and in my opinion, it has enough advantages in terms of performance and flexibility that I tend to use it as my default pattern. In your case it gives the flexibility to use a like statement and should not give any issues with a character length in the rmles column.

select      [Site],
            PersonId,
            VisitId,
            RepeatNumber,

            [Conmoción medular (sin signos detectables de lesión)] = 
                max(case when rmles like '%Conmoción medular (sin signos detectables de lesión)%' then 'Y' else 'N' end),

            Edema = max(case when rmles like '%Edema%' then 'Y' else 'N' end),
            [Contusión hemorrágica] = max(case when rmles like '%Contusión hemorrágica%' then 'Y' else 'N' end),
            [Hematoma medular] = max(case when rmles like '%Hematoma medular%' then 'Y' else 'N' end),
            [Transección medular] = max(case when rmles like '%Transección medular%' then 'Y' else 'N' end),
            [Hematoma epidural] = max(case when rmles like '%Hematoma epidural%' then 'Y' else 'N' end)

from        mnr_database -- but you mean 'table', right?
group by    [Site],
            PersonId,
            VisitId,
            RepeatNumber

I would probably make 'Conmoción medular' the title of the first pivoted column, even though it picks up a larger text, but that's up to you.

More importantly, I would question your underlying design. If these are relevant points of data, don't capture them via a text field.

How to Pivot Text Columns in SQL Server