SQL Server Pivot Perhaps

SQL Server PIVOT perhaps?

You can use a PIVOT clause. Your query could be something like this:

WITH Source as (
    SELECT Name1, Name2, [Value]
    FROM mytable 
)

SELECT Name2, CASE WHEN A IS NOT NULL THEN A ELSE 'your string' END As A
, CASE WHEN B IS NOT NULL THEN B ELSE 'your string' END As B
FROM (
        SELECT Name2, Name1, [Value]
        FROM Source
) s
PIVOT 
(
    MAX([Value]) FOR Name1 IN (A, B) -- any other Name1 would go here
) p

using your sample data above, my results were

P1  1           3
P2  1           1
P3  2           your string
P4  your string 1

EDIT:

Since you have an unknown number of columns, you will need to look at using dynamic SQL and there are several answers here on SO about that with PIVOT.

SQL Server 2005 Pivot on Unknown Number of Columns

Pivot Table and Concatenate Columns

Sql Server Pivot with lookup table

I'm kind of out of time right now, but couldn't help giving you some pointers.:

1. Assemble your basic (non pivot) query. You should use a couple of LEFT JOIN between your tables. Once you have that, proceed to Pivoting.
2. Look at the stuff I've written on Dynamic PIVOT:
   
   
   • SQL Server PIVOT perhaps?
   • Pivot data in T-SQL
   • How do I build a summary by joining to a single table with SQL Server?

sql server pivot string from one column to three columns

You can get the results you desire by taking max(school) for each pivot value. I'm guessing the pivot value you want is rank over school partitioned by student. This would be the query for that:

select * from
(select *, rank() over (partition by studid order by school) rank from student) r
pivot (max(school) for rank in ([1],[2],[3])) pv

note that max doesn't actually do anything. the query would return the same results if you replaced it with min. just the pivot syntax requires the use of an aggregate function here.

Null in SQL server pivot?

Use:

SELECT pvt.Name
    , isnull(pvt.[vocals], '') [vocals]
    , isnull(pvt.[guitar], '') [guitar]
    , isnull(pvt.[bass], '') [bass]
    , isnull(pvt.[drums], '') [drums]
FROM PivotSource  
PIVOT
(
    max(tool) 
    FOR tool 
    IN ([vocals], [guitar], [bass], [drums]) 
) pvt;

Output:

Name       vocals     guitar     bass       drums
---------- ---------- ---------- ---------- ----------
george     vocals                           
john       vocals     guitar                
paul       vocals                bass       
ringo      vocals                           drums
royi       vocals     guitar                
yoko       vocals                           

MS SQL Server pivot table with subquery in column clause

for dynamic number of columns you have to use dynamic SQL

declare
    @cols nvarchar(max),
    @stmt nvarchar(max)

select @cols = isnull(@cols + ', ', '') + '[' + T.POINTNAME + ']' from (select distinct POINTNAME from TABLE1) as T

select @stmt = '
    select *
    from TABLE1 as T
        pivot 
        (
            max(T.VALUE)
            for T.POINTNAME in (' + @cols + ')
        ) as P'

exec sp_executesql  @stmt = @stmt

SQL FIDDLE EXAMPLE

SQL Server 2005 Pivot on Unknown Number of Columns

I know you said no dynamic SQL, but I don't see any way to do it in straight SQL.

If you check out my answers to similar problems at Pivot Table and Concatenate Columns and PIVOT in sql 2005

The dynamic SQL there is not vulnerable to injection, and there is no good reason to prohibit it. Another possibility (if the data is changing very infrequently) is to do code-generation - instead of dynamic SQL, the SQL is generated to a stored procedure on a regular basis.

T SQL Pivot still one row per pivot column

You haven't provided sample data, so I'll explain your issue with an example. Let's say I have a very simple table with some very simple values:

DECLARE @T TABLE
(
    MainColumn INT NOT NULL,
    PivotColumn INT NOT NULL,
    SumColumn INT NOT NULL
);

INSERT @T VALUES
(1, 1, 10), (1, 1, 20), (1, 1, 30),
(1, 2, 60),
(1, 3, 50),
(2, 1, 10), (2, 1, 15),
(2, 2, 20),
(3, 1, 10),
(3, 2, 10),
(4, 1, 150);

If I perform the following query:

SELECT MainColumn,
       PivotColumn,
       PivotValue = SUM(SumColumn),
       OtherSum = SUM(SumColumn / 5)
FROM @T
GROUP BY MainColumn, PivotColumn
ORDER BY MainColumn, PivotColumn

I get:

+------------+-------------+------------+----------+
| MainColumn | PivotColumn | PivotValue | OtherSum |
+------------+-------------+------------+----------+
|          1 |           1 |         60 |       12 |
|          1 |           2 |         60 |       12 |
|          1 |           3 |         50 |       10 |
|          2 |           1 |         25 |        5 |
|          2 |           2 |         20 |        4 |
|          3 |           1 |         10 |        2 |
|          3 |           2 |         10 |        2 |
|          4 |           1 |        150 |       30 |
+------------+-------------+------------+----------+

Now if I use a PIVOT to pivot the PivotValue for each PivotColumn, it's going to group by MainColumn AND OtherSum column. A pivot groups by every column that isn't part of the pivot.

So my result set will be split into (MainColumn=1, OtherSum=12), (MainColumn=1, OtherSum=10), (MainColumn=2, OtherSum=5), (MainColumn=2, OtherSum=4), etc... I will get a new line for each of these values. If the OtherSum value was unique for each line, I'd expect 8 lines with a pivot.

If I remove OtherSum from my result set, my result set is just going to group by MainColumn alone, so it'll all be on one line for each distinct MainColumn value, since that's the only column the pivot would group by.

If getting the other sum value is important, I can do something like the following:

SELECT P.MainColumn,
       Val1A = P.[1],
       Val1B = P.[1] / 5,
       Val2A = P.[2],
       Val2B = P.[2] / 5,
       Val3A = P.[3],
       Val3B = P.[3] / 5
FROM
(
    SELECT MainColumn,
           PivotColumn,
           PivotValue = SUM(SumColumn)
    FROM @T
    GROUP BY MainColumn, PivotColumn
) AS T
PIVOT
(
    SUM(PivotValue) FOR PivotColumn IN ([1], [2], [3])
) AS P;

Pivot Table and Concatenate Columns

SQL Server 2005 offers a very useful PIVOT and UNPIVOT operator which allow you to make this code maintenance-free using PIVOT and some code generation/dynamic SQL

/*
CREATE TABLE [dbo].[stackoverflow_159456](
    [ID] [int] NOT NULL,
    [TYPE] [char](1) NOT NULL,
    [SUBTYPE] [char](1) NOT NULL,
    [COUNT] [int] NOT NULL,
    [MONTH] [datetime] NOT NULL
) ON [PRIMARY]
*/

DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + PIVOT_CODE + ']'
        ,@select_list = COALESCE(@select_list + ', ', '') + 'ISNULL([' + PIVOT_CODE + '], 0) AS [' + PIVOT_CODE + ']'
FROM (
    SELECT DISTINCT [TYPE] + '_' + SUBTYPE AS PIVOT_CODE
    FROM stackoverflow_159456
) AS PIVOT_CODES

SET @sql = '
;WITH p AS (
    SELECT ID, [MONTH], [TYPE] + ''_'' + SUBTYPE AS PIVOT_CODE, SUM([COUNT]) AS [COUNT]
    FROM stackoverflow_159456
    GROUP BY ID, [MONTH], [TYPE] + ''_'' + SUBTYPE
)
SELECT ID, [MONTH], ' + @select_list + '
FROM p
PIVOT (
    SUM([COUNT])
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt
'

EXEC (@sql)

---

Related Topics