SQL query - converting rows of a table into columns

Question

I have a weird question of SQL.

I have 2 tables, say products(with columns pid and pname) and rules(with columns rid and rname), and these two tables have nothing in common. The product table has 100 rows and the rule table has 16 rows.

I need to write a query in such a way that the result that I get has the pname as the first column, and the rest of the columns are the 16 rows of the table rule. In other words, I need to convert the rows of the table rule in the columns of the result. So, in the result, I should be able to have 100 rows(all pnames from product table) and 17 columns(pname and 16 rows from rule table).

Can you please help me write such a query? I tried using the case statements to convert the rows into columns, but when combined with the result of the product table, the number of rows that i get is a cartesian product of both the product and the rule tables, which I dont want. I want the number of rows to be equal to the rows in the product table, and the number of columns to be equal to the rows in the rule table. Can you please help?

Answer 1

If you are using SQL Server 2005+ you can use Pivot and Unpivot commands to convert rows to columns and columns to rows.

Answer 2

This is called a Pivot. To have a dynamic number of columns, you will probably have to use dynamic SQL- creating a query on the fly from another query, and using whatever eval method is available. Depending on what language you are using, this may be easily handled by the calling code. I would recommend refactoring to do it another way, or get ready for a few hours of learning. I can't really help you with the query without more info- the db schema, and what sql db you're using.

Here's a fairly complicated example, although I'm not sure how helpful it would be.

ALTER PROCEDURE [dbo].[CMS_GetCollection]
    @sectionContentTemplateID int
    ,@count int = null
AS
BEGIN
    SET NOCOUNT ON;
SET NOCOUNT ON;

    declare @columns varchar(max)
    select 
     @columns = COALESCE(@columns + ',[' + cte.name + '/' + a.name + ']', '[' + cte.name + '/' + a.name + ']')
    from tcmssection_contenttemplate sct 
       inner join tcmssection s on s.sectionid = sct.sectionid
       inner join tcmscontenttemplate ct on ct.contenttemplateid = sct.contenttemplateid
       inner join tcmscontenttemplate_element cte on cte.contenttemplateid = ct.contenttemplateid
       inner join tcmselement e on cte.elementid = e.elementid
       inner join tcmsattribute a on e.elementid = a.elementid 
    where 
     a.isadmin = 0
     and sct.sectioncontenttemplateid = @sectionContentTemplateID

    declare @query varchar(max)
    set @query = 
    'select ' + case when @count is not null then 
    ' top ' + convert(varchar(10),@count) else '' end + '
     [url]
     ,pageId,pageName,sortOrder
     ,[date]
     , ' + @columns + '
    from (
     select 
      s.domainpath + p.filename as [url]
      ,cte.name + ''/'' + a.name as [element.attribute]
      ,isnull(pva.valuevarchar, isnull(pva.valuetext,'''')) as [value]
      ,pv.datepublished as [date],isnull(p.sortOrder,0) as sortOrder,p.pageID,p.name as pageName
       from tcmssection_contenttemplate sct 
      inner join tcmssection s on s.sectionid = sct.sectionid
      inner join tcmspage p on p.sectioncontenttemplateid = sct.sectioncontenttemplateid
      inner join tcmscontenttemplate ct on ct.contenttemplateid = sct.contenttemplateid
      inner join tcmscontenttemplate_element cte on cte.contenttemplateid = ct.contenttemplateid
      inner join tcmselement e on cte.elementid = e.elementid
      inner join tCMSPageVersion pv on p.pageID = pv.pageID
       and pv.pageVersionID = (
        select top 1 pageVersionID 
        from tCMSPageVersion 
        where pageID = p.pageID and datePublished is not null and datecancelled is null 
        order by datePublished desc
       )
      inner join tcmsattribute a on e.elementid = a.elementid
      left outer join tcmspageversion_element pve on pve.pageversionid = pv.pageversionid 
       and pve.elementid = e.elementid and pve.name = cte.name
      left outer join tcmspageversion_attribute pva on pva.attributeid = a.attributeid 
       and pve.pageversionelementid = pva.pageversionelementid
     where 
      p.isDeleted = 0
      and a.isadmin = 0
      and sct.sectioncontenttemplateid = ' + convert(varchar(10), @sectionContentTemplateID) + '

    ) [data]
    pivot (
     max([value])
     for [element.attribute] 
     in ( ' + @columns +  ' )
    ) as [pivot] 
    order by [date] desc'

    execute(@query)

END

Answer 3

Honestly, I had to deal with this a while back for an internal data processing problem (to do with the maximum length of an SQL Row being 24000 Bytes or so). My solution was to use the XML datatype as it enables you to do most of these random kinds of data transformations with as little hassle as possible. The problem with a Pivot as the other authors suggest is that Pivots rely on Aggregate Functions to succeed - something I never wanted to do.

Answer 4

This will work fine for a set number of columns. You can also use dynamic SQL to come up with something like this if the number of columns you need will change.

SELECT
  products.pname,
  MAX(CASE WHEN rules.rname = 'Rule 0' THEN products.pid * rules.rid) AS pid_r0,
  MAX(CASE WHEN rules.rname = 'Rule 1' THEN products.pid * rules.rid) AS pid_r1,
  MAX(CASE WHEN rules.rname = 'Rule 2 'THEN products.pid * rules.rid) AS pid_r2,
  ...
FROM products
CROSS JOIN rules
GROUP BY products.pname;