Display data horizontally in SQL Server

Question

I want to display the results horizontally.

This is the table sample data:

type                        unite            Item_ID
-------------------------- ---------------- -----------
First                         NULL                1
First                         NULL                2
First                         NULL                3
Second                        Unite1              1
Second                        Unite2              2
Second                        NULL                3

and I want to display the data like this :

Type                   Item_unite_ID 1             Item_unite_ID 2      Item_unite_ID 3    

--------------------  -------------------------  --------------------- -----------
First                    NULL                            NULL                NULL        
Second                   Unite1                          Unite2              NULL

How I can do that?

Have you looked at using pivots. Also, what have you tried so far? — Peter Smith, Apr 15 '20 at 16:35
The pivot Operator need to aggregate Function and And I do not need it Now — Ahmed Omar Ahmed, Apr 15 '20 at 16:42

score 1 · Answer 1 · answered Apr 15 '20 at 17:46

Create a pivot table like this :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;

namespace ConsoleApplication8
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("type", typeof(string));
            dt.Columns.Add("unite", typeof(string));
            dt.Columns.Add("Item_ID", typeof(int));

            dt.Rows.Add(new object[] { "First", "NULL", 1 });
            dt.Rows.Add(new object[] { "First", "NULL", 2 });
            dt.Rows.Add(new object[] { "First", "NULL", 3 });
            dt.Rows.Add(new object[] { "Second", "Unite", 1 });
            dt.Rows.Add(new object[] { "Second", "Unite", 2 });
            dt.Rows.Add(new object[] { "Second", "NULL", 3 });

            int[] ids = dt.AsEnumerable().Select(x => x.Field<int>("Item_Id")).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Type", typeof(string));
            foreach (int id in ids)
            {
                pivot.Columns.Add("Item_unite_ID " + id.ToString(), typeof(string));
            }

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("type"));

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Type"] = group.Key;
                foreach (DataRow row in group)
                {
                    newRow["Item_unite_ID " + row.Field<int>("Item_ID").ToString()] = row.Field<string>("unite");
                }
            }


        }
    }


}

score 0 · Accepted Answer · answered Apr 15 '20 at 17:52

You can also achieve that with the following:

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

SET @cols = STUFF((select distinct ', 
            MAX(CASE WHEN [Item_ID]=''' + CAST([Item_ID] as varchar(10)) + ''' THEN unite ELSE NULL END) AS [Item_unite_ID ' + CAST([Item_ID] as varchar(10)) + ']'
            FROM #t 
            FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
SET @query = 'SELECT [type], ' + @Cols + '  FROM #t group by [type]' 

print (@query)
exec(@query)

Please find the db<>fiddle here.

Display data horizontally in SQL Server

2 Answers2