i want a pivot query with no aggregates sql

Question

hello I have the following table

ITEM            MarkRange
ENG-MA          20-39%
A1-014          40-59%
A2-10           1-9%
15-69           20-39%

however I want a pivot query that will make my results look like this

 20-39%   40-59%  1-9%
 ENG-MA   A1-014  A2-10  
 15-69 

I have written the PIVOT query using the max on the Item column, This however hasn't worked as it only returned one of the items for each markrange. Any suggestions will be welcome .thanks

EDIT:here is my query

select *
from 
(
  select ITEM, MarkRange
  from #unw
) src
pivot
(
  max(item )
  for MarkRange in ([1.9%], [20-39%], [10-19%])
) piv;

However I get only 1 line for each markup when the markup value contains loads of items

Answer 1

Hmm, second question I've seen today requiring a similar approach.

The basic difficulty of the approach is that each column is treated like a bucket, where items should be listed from the top, and there is no relationship (other than their line number) between adjacent values across each row.

Note this code is untested - I'm not sure off the top of my head whether you can have percent symbols in the column headers.

Also, you need to reference a numbers table - which is straightforward to implement if you don't already have one, and I'll leave as an exercise for the reader.

EDIT: forgot to include the MarkRange conditions on each join!

WITH base_data AS
(
    SELECT
        Item
        ,MarkRange
        ,ROW_NUMBER() OVER (PARTITION BY MarkRange ORDER BY Item) AS line_num

    FROM 
        #unw
)

,row_structure AS
(
    SELECT
        number AS line_num

    FROM
        numbers_table --you need to either reference a numbers table or a number sequence generator here

    WHERE
        number BETWEEN 1 AND (SELECT MAX(line_num) FROM base_data)
)

SELECT
    twenty_to_thirtynine.Item   AS [20-39%]
    ,forty_to_fiftynine.Item    AS [40-59%]
    ,one_to_nine.Item           AS [1-9%]

FROM 
    row_structure

LEFT JOIN
    base_data AS one_to_nine
    ON (one_to_nine.line_num = row_structure.line_num)
    AND (one_to_nine.MarkRange = '1-9%')

LEFT JOIN
    base_data AS twenty_to_thirtynine
    ON (twenty_to_thirtynine.line_num = row_structure.line_num)
    AND (twenty_to_thirtynine.MarkRange = '20-39%')

LEFT JOIN
    base_data AS forty_to_fiftynine
    ON (forty_to_fiftynine.line_num = row_structure.line_num)
    AND (forty_to_fiftynine.MarkRange = '40-59%')

ORDER BY
    row_structure.line_num

Answer 2

A simple solution where the markrange is known might be to use conditional aggeregation.

drop table t;
go
create table t(ITEM   varchar(20),         MarkRange varchar(20))
insert into t values 
('ENG-MA'  ,        '20-39%'),
('A1-014'  ,        '40-59%'),
('A2-10'   ,        '1-9%'),
('15-69'   ,        '20-39%')

select --rn,
    max(case when markrange = '1-9%' then item else '' end) as '1-9%',
    max(case when markrange = '20-39%' then item else '' end) as '20-39%',
    max(case when markrange = '40-59%' then item else '' end) as '40-59%'
from
(
select markrange, item , row_number() over (partition by markrange order by item) rn from t
) s
group  by rn

1-9%                 20-39%               40-59%
-------------------- -------------------- --------------------
A2-10                15-69                A1-014
                     ENG-MA               

(2 row(s) affected)

If the markrange are unknown then create the sql statement programmatically and run dynamic sql.

Answer 3

Is this what you want ? Concatenating item values under mark ranges?

I used STUFF and FOR XML PATH('') method for aggregating items under ranges seperated by comma.

--create table #unw( ITEM varchar(10), MarkRange varchar(10))
/*
insert into #unw values 
('ENG-MA','20-39%'),
('A1-014','40-59%'),
('A2-10','1-9%'),
('15-69','20-39%')
*/
;with cte as (
    select
        case when MarkRange = '1-9%' then item end as [1-9%],
        case when MarkRange = '20-39%' then item end as [20-39%],
        case when MarkRange = '40-59%' then item end as [40-59%]
    from #unw
)
select distinct
    stuff(
    (
        select isnull(',' + [1-9%],'')
        from cte
        for xml path('')
    ),1,1,'') as [1-9%],
    stuff(
    (
        select isnull(',' + [20-39%],'')
        from cte
        for xml path('')
    ),1,1,'') as [20-39%],
    stuff(
    (
        select isnull(',' + [40-59%],'')
        from cte
        for xml path('')
    ),1,1,'') as [40-59%]
from cte

Output of the query is as follows

I'm replacing the comma ',' with Carriage Return + Line Feed as follows

;with cte as (
    select
        case when MarkRange = '1-9%' then item end as [1-9%],
        case when MarkRange = '20-39%' then item end as [20-39%],
        case when MarkRange = '40-59%' then item end as [40-59%]
    from #unw
)
select
    replace(cast([1-9%] as varchar(30)), ',', CHAR(13)+CHAR(10))   [1-9%],
    replace(cast([20-39%] as varchar(30)), ',', CHAR(13)+CHAR(10)) [20-39%],
    replace(cast([40-59%] as varchar(30)), ',', CHAR(13)+CHAR(10)) [40-59%]
from
(
select distinct
    stuff(
    (
        select isnull(',' + [1-9%],'')
        from cte
        for xml path('')
    ),1,1,'') as [1-9%],
    stuff(
    (
        select isnull(',' + [20-39%],'')
        from cte
        for xml path('')
    ),1,1,'') as [20-39%],
    stuff(
    (
        select isnull(',' + [40-59%],'')
        from cte
        for xml path('')
    ),1,1,'') as [40-59%]
from cte
) t

Answer 4

I don't think you can use pivot with no aggregates. An alternative could be use row_number and full join.

Something like this:

;WITH T AS (
    SELECT *
    FROM (VALUES
        ('ENG-MA','20-39%')
        ,('A1-014','40-59%')
        ,('A2-10','1-9%')
        ,('15-69','20-39%')
    ) AS V(ITEM, MarkRange)
)
, R1 AS (
SELECT
    ITEM
    , ROW_NUMBER() OVER (ORDER BY ITEM) AS ID
FROM T WHERE MarkRange = '20-39%'
)
, R2 AS (
SELECT
    ITEM
    , ROW_NUMBER() OVER (ORDER BY ITEM) AS ID
FROM T WHERE MarkRange = '40-59%'
)
, R3 AS (
SELECT
    ITEM
    , ROW_NUMBER() OVER (ORDER BY ITEM) AS ID
FROM T WHERE MarkRange = '1-9%'
)
SELECT R1.ITEM AS [20-39%], R2.ITEM AS [40-59%], R3.ITEM AS [1-9%]
FROM R1
FULL JOIN R2 ON R1.ID = R2.ID
FULL JOIN R3 ON R1.ID = R3.ID OR R2.ID = R3.ID
;