Get data from multiple columns into a single column

Posted on 2012-08-28
Medium Priority
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
  • 2
LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38340940
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

ID: 38345231
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

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

Author Closing Comment

ID: 38346663
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 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