SQL query - converting rows of a table into columns

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 num开发者_高级运维ber of columns to be equal to the rows in the rule table. Can you please help?

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

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;

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

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.