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
Solved

SQL Transpose rows to columns

Posted on 2009-05-18
4
932 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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) …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

839 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