Background:
I am working on Sql2000 query where I need to get a cross tab query report. I tried few combinations and got the results using RollUP.
Issue
The real problem is that I have to insert the records in a table which has only one column.
For instance:
My actual records (after RollUP) have 3 columns and are as follow:
ID|May|Jun|Total
CS|21 |54 |75
AB|12 |15 |27
(These results are coming in grid format)
The problem I need to insert in them in a table having only 1 column. Format would be
Data
ID May Jun Total
21 54 75
12 15 27
Please note that column header is also inserted in the second table.
Does this makes sense?
I know this can be done easily with reporing services or crystal reports but I have limiation of not going for them.
Thanks in advance.
It sounds like all you want to do is concatenate the strings of the three (or four?) columns into one?
I'll save you the bad practice lecture that usually accompanies this type of question:
Based on your example, I am assuming you want fixed length fields, and not delimited ones. If you want delimited, use varchar instead of char and put delimiting strings in between. The last column is cast as varchar since you don't need the extra padding.
SELECT CAST([ID] AS char(10))
+ CAST(May AS char(10))
+ CAST(June AS char(10))
+ CAST([Total] AS varchar(10)) AS column1
FROM ...
This would result in
CS 21 54 75
AB 12 15 27