Home Forums SQL Server 2012 SQL Server 2012 - T-SQL de-aggregate or Un-aggregate rows. whats best method? RE: de-aggregate or Un-aggregate rows. whats best method?

  • October 20, 2014 at 5:16 am

    #1753462

    You could use an inline tally table too:

    CREATE TABLE #MyTable (OrderID INT IDENTITY(1,1), ItemName VARCHAR(20), UnitPrice INT, Quanity INT)

    INSERT INTO #MyTable (ItemName, UnitPrice, Quanity)

    VALUES ('car', 10, 2), ('Train', 20, 1), ('boat', 30, 4), ('plane', 10, 2);

    WITH

    e1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

    e2 AS (SELECT a.n FROM e1 a, e1 b),

    iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM e2 a, e2 b)

    SELECT *

    FROM #MyTable

    CROSS APPLY (SELECT TOP(Quanity) n FROM iTally) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden