Get data from multiple columns into a single column

Posted on 2012-08-28
Last Modified: 2012-08-29
I am not sure why I can’t figure this out but I am at a road block and am looking for assistance on this.  We are using MS SQL 2008 and I need a query that I can use with other queries and VB application with Crystal Reports.

I have a child table that sores data in 15 different fields and there can be multiple child records per parent.

ParentKey, LineNo, Field1…Field15

The LineNo records the number of lines 1…4…10…etc. in order.

What I would like to do is get the data in a different format like the below:

ParentKey       LineNo       FieldName      Value      Count
1            1      Fld01            aaa      1
1            1      Fld02            bbb      2

1            1      Fld15            ccc      15
1            2      Fld01            ddd      16

1            2      Fld15            zzz      30

Thank you for your help and suggestions.
Question by:wsadfilm
    LVL 25

    Accepted Solution

    Firstly ... you could UNPIVOT the columns into rows ... e.g.
    ;with sample_data as (
    select 1 ParentKey, 1 [LineNo], 'aa01' Field1, 'aa02' Field2, 'aa15' Field15 union all
    select 1 ParentKey, 2 [LineNo], 'bb01' Field1, 'bb02' Field2, 'bb15' Field15
    select ParentKey, [LineNo], FieldName, [Value]
    from sample_data
    unpivot([Value] for FieldName in ([Field1],[Field2],[Field15]) ) upv

    Open in new window

    LVL 12

    Expert Comment

    Not very optimal but some workaround that solves your problem

    declare @TempTab table (RowNum int identity(1,1), ParentKey int, LineNum int)
    insert into @TempTab (ParentKey, LineNum) select ParentKey, LineNum from YourSourceTable

    declare @i as int, @j as int, @cnt as int, @ParentKey int, @LineNum int
    select @cnt = COUNT(*) from @TempTab
    set @i = 1
    while @i <= @cnt
          select @ParentKey = ParentKey, @LineNum = LineNum from @TempTab where RowNum = @i
          set @j = 1
          while @j <= 15
                declare @qry nvarchar(max), @FieldName varchar(10), @Value varchar(10)
                if @j < 10
                      set @FieldName = 'Fld0' + CAST(@j as varchar(1))
                      set @FieldName = 'Fld' + CAST(@j as varchar(2))
                set @qry = 'SELECT @Value1 = ' + @FieldName + ' from YourSourceTable where ParentKey = ' + CAST(@ParentKey as varchar) + ' and LineNum = ' + CAST(@LineNum as varchar)
                EXEC sp_executesql @qry, N'@Value1 varchar(10) OUTPUT', @Value OUTPUT
                insert into YourTargetTable (ParentKey, LineNum, FieldName, Value)
                values (@ParentKey, @LineNum, @FieldName, @Value)
                set @j = @j + 1
          set @i = @i + 1
    LVL 12

    Expert Comment

    Pls replace YourSourceTable and YourTargetTable placeholders in above script with relevant names before you run this script

    Author Closing Comment

    Thanks for the ideas - I am accepting the one I went with.  It seems to be a bit cleaner and easier to follow.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now