Solved

SQL Transpose rows to columns

Posted on 2009-05-18
4
946 Views
Last Modified: 2012-06-21
I am trying to solve a problem in SQL but can't seem to get it solved.
I need to get sql 2000 table information into a table that resides within a Word Document that looks just like this.

Medical Payments:      $ 1,001      $ 1,005      $ 1,002
Perils Deductible:      $ 1,001      $ 1,010      $ 1,001
Hurricane Deductible:      2%      3%      2%
Premium:                            $ 401      $ 405      $ 410
Company:            XXXCo           YYYCo        ZZZCo

The SQl 2000 table looks like this:
company              Premium                   PerilsDeduct                Hurricandeduct             Medical
xxxco                     401                             1001                                 2                          1001
zzzco                     410                             1001                                 2                          1002
yyyco                     405                             1010                                 3                          1005  


The column on the left will NOT change when merging. So you can see I need to transpose rows into columns. It's not going to be simply a "1 to 1"  transpose because I'll be selecting what records to merge based on another value that is not in this SQL table.  I think this is a tough issue and would be very grateful for help.  I can't use the pivot function because it's sql 2000.

 
0
Comment
Question by:silverbuck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24414085
You got to introduce comma as a separator between each piece of data in a word document row OR make it tab delimted. For this copy into TextPad or any decent text editor, replace all % and $ with nothing and let us say XXX with xxx.

You can replace all intelligently to get commas between data cells. Now, whether comma separated of tab delimited, save it as a .csv file and open in excel. Copy entire data in excel and with shift pressed right click choosing paste special and in this choose transpose option. You will get the data the way you want it. Now load the resulting saved .csv file using bcp etc. Now you need to apply what records to merge based on another value that is not in this SQL table. If you cannot post more details about this conditional merging.
0
 
LVL 4

Accepted Solution

by:
TimSledge earned 500 total points
ID: 24414701
Run the script below to see an example of how you can get the data into the format you want in SQL before sending it to Word.
Hope this is helpful.
Tim Sledge
create table dbo.tblData (DataID int identity(1,1), DataText varchar(20))
 
insert into dbo.tblData (DataText) select 'AAAA'
insert into dbo.tblData (DataText) select 'BBBB'
insert into dbo.tblData (DataText) select 'CCCC'
insert into dbo.tblData (DataText) select 'DDDD'
insert into dbo.tblData (DataText) select 'EEEE'
insert into dbo.tblData (DataText) select 'FFFF'
insert into dbo.tblData (DataText) select 'GGGG'
insert into dbo.tblData (DataText) select 'HHHH'
insert into dbo.tblData (DataText) select 'IIII'
insert into dbo.tblData (DataText) select 'JJJJ'
insert into dbo.tblData (DataText) select 'KKKK'
 
create table dbo.tblOutput
(
ID int identity(1,1),
ColumnA varchar(20),
ColumnB varchar(20),
ColumnC varchar(20),
ColumnD varchar(20),
ColumnE varchar(20),
)
 
 
DECLARE @Counter INT,  @CounterColumn INT, @CounterMAX INT, @ColumnA varchar(20), @ColumnB varchar(20), @ColumnC varchar(20), @ColumnD varchar(20), @ColumnE varchar(20)
 
SET @Counter = 1 --Will move sequentially through the records starting with record one
SET @CounterColumn=1
 
--Get the highest sequential record number before starting
SELECT @CounterMAX = max(DataID)
FROM dbo.tblData
 
        WHILE @CounterColumn <=5 and @Counter <= @CounterMAX
	       
	       BEGIN
		
			IF @CounterColumn =1 
			    BEGIN 
			       SELECT @ColumnA = dbo.tblData.DataText  FROM  dbo.tblData WHERE DataID = @Counter
		      	END
		      	IF @CounterColumn =2 
		      	    BEGIN   SELECT @ColumnB = dbo.tblData.DataText  FROM  dbo.tblData WHERE DataID = @Counter
			     END
			     IF @CounterColumn =3 
			         BEGIN     
			             SELECT @ColumnC = dbo.tblData.DataText  FROM  dbo.tblData WHERE DataID = @Counter
				    END
				IF @CounterColumn =4 
				    BEGIN    
				        SELECT @ColumnD = dbo.tblData.DataText  FROM  dbo.tblData WHERE DataID = @Counter
				    END
				IF @CounterColumn =5 
				    BEGIN
				         SELECT @ColumnE = dbo.tblData.DataText  FROM  dbo.tblData WHERE DataID = @Counter 
				     END
            
               SET @CounterColumn = @CounterColumn + 1
            
               IF @CounterColumn=6 or (@CounterMAX=@Counter) 
                   BEGIN
                     SET @CounterColumn=1 
                     
                     INSERT INTO dbo.tblOutput(  ColumnA, ColumnB, ColumnC, ColumnD, ColumnE) SELECT @ColumnA, @ColumnB, @ColumnC, @ColumnD, @ColumnE
                    
                     SET @ColumnA=''
                     SET @ColumnB=''
                     SET @ColumnC=''
                     SET @ColumnD=''
                     SET @ColumnE='' 
                   
                   END
             
               SET @Counter= @Counter +1
            
            END
            
       
  SELECT
ID ,
ColumnA ,
ColumnB ,
ColumnC ,
ColumnD ,
ColumnE 
FROM dbo.tblOutput

Open in new window

0
 

Author Comment

by:silverbuck
ID: 24417068
Thanks you for this. I will test it at my frst opportunity.
0
 

Author Closing Comment

by:silverbuck
ID: 31582667
Thanks a lot. I appreciate it. You helped me somewhat.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
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…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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