Asked  7 Months ago    Answers:  5   Viewed   364 times

I have a table like this...

CustomerID   DBColumnName   Data
--------------------------------------
1            FirstName      Joe
1            MiddleName     S
1            LastName       Smith
1            Date           12/12/2009
2            FirstName      Sam
2            MiddleName     S
2            LastName       Freddrick
2            Date           1/12/2009
3            FirstName      Jaime
3            MiddleName     S
3            LastName       Carol
3            Date           12/1/2009

And I want this...

Is this possible using PIVOT?

CustomerID  FirstName   MiddleName          LastName        Date
----------------------------------------------------------------------
1           Joe             S               Smith           12/12/2009
2           Sam             S               Freddrick       1/12/2009
3           Jaime           S               Carol           12/1/2009

 Answers

58

You can use the MAX aggregate, it would still work. MAX of one value = that value..

In this case, you could also self join 5 times on customerid, filter by dbColumnName per table reference. It may work out better.

Tuesday, June 1, 2021
 
Sauleil
answered 7 Months ago
79

You can use the PIVOT function to get the result, you will just have to use row_number() to help.

The base query for this will be:

select skill_id, skill, parameter,
  row_number() over(partition by skill, skill_id order by skill_id) rn
from yt;

See SQL Fiddle with Demo. I use row_number() to apply a distinct value to each row within the skill and skill_id, you will then use this row number value as the column to PIVOT.

The full code with the PIVOT applied will be:

select skill_id, skill,[Parameter_1], [Parameter_2], [Parameter_3]
from 
(
  select skill_id, skill, parameter,
    'Parameter_'+cast(row_number() over(partition by skill, skill_id 
                                     order by skill_id) as varchar(10)) rn
  from yt
) d
pivot
(
  max(parameter)
  for rn in ([Parameter_1], [Parameter_2], [Parameter_3])
) piv;

See SQL Fiddle with Demo.

In your case, it seems like you will have an unknown number of parameters for each skill. If that is true, then you will want to use dynamic SQL to get the result:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Parameter_'
                          +cast(row_number() over(partition by skill, skill_id 
                               order by skill_id) as varchar(10))) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT skill_id, skill,' + @cols + ' from 
             (
                select skill_id, skill, parameter,
                  ''Parameter_''+cast(row_number() over(partition by skill, skill_id 
                                   order by skill_id) as varchar(10)) rn
                from yt
            ) x
            pivot 
            (
                max(parameter)
                for rn in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo

Sunday, July 4, 2021
 
Novalirium
answered 6 Months ago
12

The simplest way to get the result would be using an aggregate function with a CASE expression:

select
  scenario_code,
  max(case when indicator = 2 then period end) [Period 2],
  max(case when indicator = 2 then value end) [Value 2],
  max(case when indicator = 3 then period end) [Period 3],
  max(case when indicator = 3 then value end) [Value 3],
  max(case when indicator = 4 then period end) [Period 4],
  max(case when indicator = 4 then value end) [Value 4],
  [area code],
  Release_Code
from yourtable
group by scenario_code, [area code], Release_Code

See SQL Fiddle with Demo

But you can use the PIVOT function to get the result but you would also need to unpivot the Period and Value columns first, since you want to pivot on two columns.

Since you are using SQL Server 2012 you can use CROSS APPLY with VALUES to unpivot. The basic syntax will be:

select scenario_code, [area code], release_code,
  col = col +' ' +cast(indicator as varchar(10)),
  val
from yourtable
cross apply
(
  values
    ('Period', convert(varchar(10), period, 120)),
    ('Value', convert(varchar(10), value))
) c (col, val);

See SQL Fiddle with Demo. This is going to get your data into the format:

| SCENARIO_CODE | AREA CODE | RELEASE_CODE |      COL |        VAL |
|---------------|-----------|--------------|----------|------------|
|             7 |     OP014 |           17 | Period 2 | 2000-06-13 |
|             7 |     OP014 |           17 |  Value 2 |       1000 |
|            16 |     OP014 |           17 | Period 2 | 2000-09-12 |
|            16 |     OP014 |           17 |  Value 2 |       1100 |

You'll notice that we had to cast/convert both columns to the same datatype in order for this unpivoting process to work. Once the data has been unpivoted, then you can easily apply the PIVOT function and convert your values in COL to the new column headers:

select scenario_code,
  [Period 2], [Value 2],
  [Period 3], [Value 3],
  [Period 4], [Value 4],
  [area code], release_code
from
(
  select scenario_code, [area code], release_code,
    col = col +' ' +cast(indicator as varchar(10)),
    val
  from yourtable
  cross apply
  (
    values
      ('Period', convert(varchar(10), period, 120)),
      ('Value', convert(varchar(10), value))
  ) c (col, val)
) d
pivot
(
  max(val)
  for col in ([Period 2], [Value 2],
              [Period 3], [Value 3],
              [Period 4], [Value 4])
) piv;

See SQL Fiddle with Demo. Both versions give a final result of:

| SCENARIO_CODE |   PERIOD 2 | VALUE 2 |   PERIOD 3 | VALUE 3 |   PERIOD 4 | VALUE 4 | AREA CODE | RELEASE_CODE |
|---------------|------------|---------|------------|---------|------------|---------|-----------|--------------|
|             7 | 2000-06-13 |    1000 | 2000-01-12 |    1300 | 2000-06-12 |     600 |     OP014 |           17 |
|            16 | 2000-09-12 |    1100 | 2000-06-17 |     500 | 2000-12-12 |     650 |     OP014 |           17 |
|            17 | 2002-06-22 |    1200 | 2008-05-04 |     550 | 2013-06-12 |     150 |     OP014 |           17 |
Saturday, August 7, 2021
 
MasterJoe
answered 4 Months ago
25

You can perform this via a PIVOT. You can use either a static PIVOT where you know the number of columns that you want to rotate or you can use a dynamic PIVOT

Static Pivot (see SQL Fiddle with Demo)

SELECT *
FROM 
(
  select *
  from t1
) x
pivot
(
  min(columnc)
  for columnb in ([X], [Y])
) p

Dynamic Pivot (see SQL Fiddle with Demo)

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

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

set @query = 'SELECT columna, ' + @cols + ' from 
             (
                select *
                from t1
            ) x
            pivot 
            (
                min(ColumnC)
                for ColumnB in (' + @cols + ')
            ) p '

execute(@query)

Both versions will give the same results. The second works when you have an unknown number of columns that will be transformed.

Wednesday, October 20, 2021
 
ALH
answered 2 Months ago
ALH
72

You can use the grouping ability of LINQ to accomplish this. Also, you can bind a DataGrid to a LINQ query, but the data will be read only.

A web search for LINQ grouping should get you where you're going.

Wednesday, November 24, 2021
 
Joegramming
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share