Building View Dynamically .. Need Help!

This is my resultset
Value Name
1 A
101 A
2 B
10 B
70 B
But I want it in this way from SQL Server
A B
1 2
101 10
70hangar18 wrote:
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
You should probably do this in your client application, but, run this script
in query analyzer:
set nocount on
select 1 Value,'A' [Name] into #temp
union all select
101 ,'A'
union all select
2 ,'B'
union all select
10 ,'B'
union all select
70 ,'B'
select * from #temp
Select
ta.A,
tb.B
From
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [A]
FROM #temp t1
WHERE t1.Name='A') ta
Full outer join
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [B]
FROM #temp t1
WHERE t1.Name='B') tb
ON ta.ID=tb.ID
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE TABLE #Test
(
col INT,
col1 CHAR(1)
)
INSERT INTO #Test VALUES (1,'A')
INSERT INTO #Test VALUES (20,'A')
INSERT INTO #Test VALUES (100,'A')
INSERT INTO #Test VALUES (10,'B')
INSERT INTO #Test VALUES (5,'B')
INSERT INTO #Test VALUES (3,'B')
SELECT
CASE WHEN col1 ='A' THEN col END AS 'A',
CASE WHEN col1 ='B' THEN col END AS 'B'
FROM #Test
"hangar18" <soni.somarajan@.wipro.com> wrote in message
news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
>|||That was my first thought as well, but it results in:
A B
1 [NULL]
101 [NULL]
[NULL] 2
[NULL] 10
[NULL] 70
Not quite what the OP wants.
Bob
Uri Dimant wrote:
> CREATE TABLE #Test
> (
> col INT,
> col1 CHAR(1)
> )
> INSERT INTO #Test VALUES (1,'A')
> INSERT INTO #Test VALUES (20,'A')
> INSERT INTO #Test VALUES (100,'A')
> INSERT INTO #Test VALUES (10,'B')
> INSERT INTO #Test VALUES (5,'B')
> INSERT INTO #Test VALUES (3,'B')
>
> SELECT
> CASE WHEN col1 ='A' THEN col END AS 'A',
> CASE WHEN col1 ='B' THEN col END AS 'B'
> FROM #Test
>
>
>
> "hangar18" <soni.somarajan@.wipro.com> wrote in message
> news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 1 Dec 2005 05:36:56 -0800, hangar18 wrote:

>This is my resultset
>Value Name
>1 A
>101 A
>2 B
>10 B
>70 B
>But I want it in this way from SQL Server
>A B
>1 2
>101 10
> 70
Hi hangar18,
Bob's post will work. But just for fun, here's another possibility:
SELECT MAX(A) AS A, MAX(B) AS B
FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
CASE WHEN a.col1 ='B' THEN a.col END AS B,
(SELECT COUNT(*)
FROM #Test AS b
WHERE b.col1 = a.col1
AND b.col <= a.col) AS Rank
FROM #Test AS a) AS d
GROUP BY Rank
ORDER BY Rank
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The basic principle of a tiered architecture is that display and
formatting is done in the front end and ** never** in the back end.
This a more basic programming principle than just SQL and RDBMS. This
should have been covered in the first year of your comp sci courses.|||When are you going to learn, perhaps you should actually do some
programming!
Formatting should be done where it is most efficient to do it, you can not
blankly state formatting be done in the front end and **never** the backend
its just not true - any programmer knows that.
Data manipulation is best done in the SQL Server and may well include
formatting, consider - paging, pivoting, security to name but a couple.
I think it is very irresponsible for you to keep taking this line when so
many times myself and other posters have given plenty of examples of when to
do formatting in the engine.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133490026.932802.161270@.o13g2000cwo.googlegroups.com...
> The basic principle of a tiered architecture is that display and
> formatting is done in the front end and ** never** in the back end.
> This a more basic programming principle than just SQL and RDBMS. This
> should have been covered in the first year of your comp sci courses.
>|||Hugo Kornelis wrote:
> SELECT MAX(A) AS A, MAX(B) AS B
> FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
> CASE WHEN a.col1 ='B' THEN a.col END AS B,
> (SELECT COUNT(*)
> FROM #Test AS b
> WHERE b.col1 = a.col1
> AND b.col <= a.col) AS Rank
> FROM #Test AS a) AS d
> GROUP BY Rank
> ORDER BY Rank
>
Clever!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"