Solved

SQL Transpose rows to columns

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
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.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

746 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

12 Experts available now in Live!

Get 1:1 Help Now