how to show row wise data columnwise data

  • Hi All,

    i am using sql server 2005.

    in a table a record is entered as product1 , status1 ,product2 , status2 , product3 , status3 in a row now i have to display it as

    product1 status1

    product2 status2

    product3 status3

    how can i do it .

    please help.

  • Since you didn't say what the datatype of the CSV column is, and since you said 2005", I can only assume the "worst". This would run much faster with a Tally table instead of a "smart" CTE that acts like one, but this works.

    The following code is fully operational test code. You'll need to make changes for the actual table and column names. Lemme know how it works out for ya...

    --===== Create a sample table with data...

    -- THIS IS NOT PART OF THE SOLUTION

    CREATE TABLE #yourtable

    (

    PK INT IDENTITY(1,1),

    CSVData VARCHAR(MAX)

    )

    INSERT INTO #yourtable

    (CSVData)

    SELECT 'product1 , status1 ,product2 , status2 , product3 , status3' UNION ALL

    SELECT 'Prod10,Status10,Prod11,Status11,Prod12,Status12,Prod13,Status13,Prod14,Status14' UNION ALL

    SELECT 'Prod15,Status15'

    ;WITH cteTally AS

    (--==== Create a "Tally Table" with a count of 1 to N where N is the max len

    -- of the CSVData column +2

    SELECT TOP (SELECT MAX(LEN(CSVData))+2 FROM #yourtable)

    ROW_NUMBER() OVER (ORDER BY t1.OBJECT_ID) AS N

    FROM Master.sys.ALL_COLUMNS t1

    CROSS JOIN

    Master.sys.ALL_COLUMNS t2

    )

    ,

    cteSplit AS

    (--==== Do the split and calculate the row and column numbers so we can reassemble later

    SELECT PK,

    Element = (RANK() OVER (PARTITION BY y.PK ORDER BY t.n)), --Just for checking

    Row = (RANK() OVER (PARTITION BY y.PK ORDER BY t.n)-1)/2+1, --Sneaky key to whole thing

    Col = (RANK() OVER (PARTITION BY y.PK ORDER BY t.n)-1)%2+1, --Sneaky key to whole thing

    Data = RTRIM(LTRIM(NULLIF(

    SUBSTRING(','+y.CSVData,

    t.N+1,

    CHARINDEX(',', y.CSVData+',', t.N+1)-t.N-1)

    ,'')))

    FROM cteTally t

    CROSS JOIN #yourtable y

    WHERE t.N < LEN(y.CSVData)

    AND SUBSTRING(','+y.CSVData, t.N, 1) = ','

    )

    --===== Reassemble the split data using the precacalculated row and column numbers

    -- using a simple "Crosstab" (as fast or faster than PIVOT in most cases)

    SELECT MIN(CASE WHEN Col = 1 THEN Data END) AS Product,

    MIN(CASE WHEN Col = 2 THEN Data END) AS [Status]

    FROM cteSplit

    GROUP BY PK,Row

    ORDER BY PK,Row

    DROP TABLE #yourtable

    By the way, in case anyone is interested... this also works for PASSING AN "ARRAY" OF INFORMATION as a parameter. This one just happens to be a 2 column "array"... changing a couple of things like /2 and %2 in a couple of spots and changing the CorssTab code (add extra columns to that), will allow you to setup for virtually any number of columns in the "array" parameter.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just curious... did that solve your problem or did you die? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hi

    i have a table like

    empname empid empsal

    ---------------------------

    abc 1 300000

    def 2 500000

    fgh 3 700000

    and op should be

    abc def fgh

    1 2 3

    300000 500000 700000

    Thanks:)

  • ashoknegi84 (4/1/2008)


    hi

    i have a table like

    empname empid empsal

    ---------------------------

    abc 1 300000

    def 2 500000

    fgh 3 700000

    and op should be

    abc def fgh

    1 2 3

    300000 500000 700000

    Thanks:)

    If you have a table with only 3 rows in it, then do it manually. 😉

    If the table has more than 3 rows in it, then you need to post more realistic data so we can show you how... please refer to the URL in my signature line before you even think of posting more data...

    Also, when you do post some data, you'll need to identify which column you want to group on... you don't have a "grouping" column as an EAV might to identify all the parts of an individual row.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ashoknegi84 (4/1/2008)


    hi

    i have a table like

    empname empid empsal

    ---------------------------

    abc 1 300000

    def 2 500000

    fgh 3 700000

    and op should be

    abc def fgh

    1 2 3

    300000 500000 700000

    Thanks:)

  • Use this command 🙂

    select rtrim (xmlagg (xmlelement (e, empno|| ',')).extract ('//text()'), ',') enames

    from emp

    By

    Nitesh Jyotirmay

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply