Expected OR Similar Output

Question

HI,

I am having data as shown below

ItemCode      Attr1    Attr1  InStock
ITEM-000001   43      S      1
ITEM-000001   52      L    2
ITEM-000006   42      R    1
ITEM-000006   44      R    2
ITEM-000009   56      R    1
ITEM-000011   40      R    2

And by using Pivot table (or any other technique) i want the reslut like as

Output for ITEM-000001 43 52 S 1
L 2 Same for others.

Kind regards, Om

Answer 1

Hi I got solution for the above view which will work for changing columns data i.e. Attr1 & Attr2

Solution is below:

CREATE TABLE #Aggregates
             (
              [On Hand] VARCHAR(50),
              ColumnText VARCHAR(50),
              CellData INT
             )

INSERT INTO #Aggregates
            (
             [On Hand],
             ColumnText,
             CellData
            )
SELECT ISNULL(matrixItem.Attribute2, '--'), matrixItem.Attribute1, item.UnitsInStock
FROM   InventoryMatrixItemView AS matrixItem LEFT OUTER JOIN
       InventoryItemView AS item ON matrixItem.MatrixItemCode = item.ItemCode
WHERE  (matrixItem.Selected = 1 AND matrixItem.ItemCode = @itemCode)

CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )

INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates 
ORDER BY       ColumnText

CREATE TABLE #Rows 
             (
              [On Hand] VARCHAR(50)
             )

INSERT INTO    #Rows
                (
                 [On Hand]
                )

SELECT DISTINCT [On Hand]
FROM #Aggregates with(nolock)

DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQL VARCHAR(1000)

SELECT      @ColumnIndex = 0,
            @MaxColumnIndex = MAX(ColumnIndex)
FROM        #Columns


WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #Columns
      WHERE      ColumnIndex = @ColumnIndex

      SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT NULL DEFAULT 0'
      EXEC       (@SQL)

      SELECT     @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
                         FROM #Aggregates, #Columns
                         WHERE #Rows.[On Hand] = #Aggregates.[On Hand] 
                         AND #Columns.ColumnText = #Aggregates.ColumnText 
                         AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQL)

      SELECT  @ColumnIndex = @ColumnIndex + 1 
   END

DROP TABLE #Columns
DROP TABLE #Aggregates

SELECT     #Rows.*
FROM        #Rows
ORDER BY   #Rows.[On Hand]

DROP TABLE  #Rows