February 25, 2011 at 11:58 am
Greetings,
CREATE TABLE Test
(
Code char(1),
Name char (3),
Data int
)
INSERT INTO Test
SELECT 'A','abc',1 UNION
SELECT 'B','bcd',2 UNION
SELECT 'C','cde',3 UNION
SELECT 'D','def',4
SELECT * FROM Test
DROP TABLE Test
I need to output data like this (with the blank lines if possible). Possible?
A
abc
1
B
bcd
2
C
cde
3
D
def
4
February 25, 2011 at 12:24 pm
Logic like this usually should be done at the presentation layer.
However, if you need to do it at the DB layer, you could use
;WITH cte AS
(
SELECT 1 AS pos,Code,DATA FROM Test
UNION ALL
SELECT 2,NAME,DATA FROM Test
UNION ALL
SELECT 3,CAST(DATA AS CHAR(3)),DATA FROM Test
UNION ALL
SELECT 4,'',DATA FROM Test
)
SELECT Code
FROM cte
ORDER BY DATA,pos
February 25, 2011 at 1:49 pm
I need to do this at the db layer because I am tasked with creating a report formatted for Blackberry but I don't have a reporting architecture just SS and SSIS.
My sincere apologies for wasting your time. I am in the wrong forum, I am using 2000 and I can't use CTE. I am going to post in an appropriate forum.
February 25, 2011 at 1:54 pm
There's no need to post in the 2000 forum in this case.
A cte is nothing but a subquery in this specific case.
You could use the following code insted:
SELECT Code
FROM (
SELECT 1 AS pos,Code,DATA FROM Test
UNION ALL
SELECT 2,NAME,DATA FROM Test
UNION ALL
SELECT 3,CAST(DATA AS CHAR(3)),DATA FROM Test
UNION ALL
SELECT 4,'',DATA FROM Test
) cte
ORDER BY DATA,pos
February 25, 2011 at 2:01 pm
I took a different approach and just formatted it...
select Code + char(13) + Name + char(13) + cast(Data as varchar(50)) + char(13)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2011 at 4:14 pm
Thank you both. Each of these solutions looks viable.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply