Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Transpose rows to columns

Posted on 2009-05-18
4
Medium Priority
?
951 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
  • 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 1500 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Suggested Courses

824 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