SQL Server Pivot – How to Convert Query Results into Groups of Common Results

cross-applypivotsql server

I have a query that returns results like this:

     MyValue        MyGroup
_______________|_______________
       56      |      I
_______________|_______________
       12      |      I
_______________|_______________
       56      |      II
_______________|_______________
       12      |      II
_______________|_______________
       56      |      III
_______________|_______________
       56      |      IV
_______________|_______________
       56      |      V
_______________|_______________
       12      |      V
_______________|_______________

I need to convert that into something like this:

      ColA           ColB             ColC            ColD           Count
_______________|_______________|_______________|_______________|_______________
       12      |      56              null            null             3
_______________|_______________|_______________|_______________|_______________
       56      |      null            null            null             2
_______________|_______________|_______________|_______________|_______________

I know I could setup an iterator and go over all the rows of my table and construct this result.

But I think there is another, "set based" way. Maybe a "Cross Apply" or "Pivot".

But it does not matter how many times I read the docs or look at examples for those, I can't seem to wrap my head around them enough to write my own.

Does anyone have a suggestion?

NOTE: In case it matters, assume that my query to get the first results looks like this:

select MyValue, MyGroup
from SomeTable

(It is actually a complex set of CTE's but I can just add another layer if need be.)

Best Answer

Seems a simpler approach:

SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM 
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) 
   FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '')
  FROM dbo.MyTable AS t
) AS x GROUP BY Val;

Related Solutions

Mysql – How to convert rows into columns and query on them in Mysql

You can try this kind of "solution":

http://sqlfiddle.com/#!9/d33a95/18

It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly.

Here are the tables / data used:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
); 
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES 
  (1, '23', 1), 
  (2, 'true', 1),
  (1, '25', 2),
  (2, 'false', 2),
  (1, '23', 3),
  (2, 'false', 3),
  (1, '23', 4), 
  (2, 'true', 4);

And the query:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
   OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;

SQL Server – Convert Row into Concatenated String with Column Names

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day.

"Drag-and-drop" the list of columns from the Object Explorer window onto a Query Window, as shown:

I'm using the dbo.spt_fallback_usg table as an example. After I drag-and-drop them into the query window, the columns are:

xserver_name, xdttm_ins, xdttm_last_ins_upd, xfallback_vstart, dbid, segmap, lstart, sizepg, vstart

Hit [CTRL + H] to open the "Search and Replace" dialog box:

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box. Pressing the "Replace All" button will put each column name on a new line. Next, modify the "Search and Replace" items like this:

This will turn each individual column name into a CASE WHEN... statement.

Now replace the "newlines" with +, as in:

This results in the following code in the query window:

CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END + 
CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END + 
CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END + 
CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END + 
CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END + 
CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END + 
CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END + 
CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END + 
CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END +

This can then be manually wrapped into a SELECT statement like:

SELECT 
    CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END + 
    CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END + 
    CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END + 
    CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END + 
    CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END + 
    CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END + 
    CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END + 
    CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END + 
    CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END 
FROM dbo.spt_fallback_usg

Best Answer

Related Solutions

Mysql – How to convert rows into columns and query on them in Mysql

SQL Server – Convert Row into Concatenated String with Column Names

Related Question