Creating a range lookup table from a file of millions of rows

I have a file with the item id and the item type. The data looks as follows
:
ItemID ItemType
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 A
9 A
I want to create a lookup tables as follows:
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
Please keep in mind the file I have the ids on is millions of rows. Also,
there are gaps in the ids, (i.e. may jump from 4 to 6 no 5). Gaps are
acceptable as long as they are not too large.
Thanks in advance for any tips you can provide.I'm not sure what you mean by "gaps are acceptable as long as they are
not too large". Apparently no gaps in your sample data anyway. See if
this meets your requirements:
SELECT MIN(itemid), MAX(itemid), itemtype
FROM
(SELECT T1.itemid, T1.itemtype,
MIN(T2.itemid) AS x_itemid
FROM tbl AS T1
LEFT JOIN tbl AS T2
ON T1.itemtype <> T2.itemtype
AND T1.itemid < T2.itemid
GROUP BY T1.itemid, T1.itemtype) AS T
GROUP BY itemtype, x_itemid
If performance is an issue then you could do this for smaller subsets
of rows and then combine the results.
David Portas
SQL Server MVP
--|||David
Can I ask you, why did you join the table?
create table #test
(
itemid int not null primary key,
itemtype char(1) not null
)
insert into #test values (1,'a')
insert into #test values (2,'a')
insert into #test values (3,'a')
insert into #test values (4,'b')
insert into #test values (5,'b')
insert into #test values (6,'c')
insert into #test values (7,'c')
insert into #test values (8,'d')
select min(itemid),max(itemid),itemtype
from #test group by itemtype
What is differ between these queries?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123000386.349876.22890@.g47g2000cwa.googlegroups.com...
> I'm not sure what you mean by "gaps are acceptable as long as they are
> not too large". Apparently no gaps in your sample data anyway. See if
> this meets your requirements:
> SELECT MIN(itemid), MAX(itemid), itemtype
> FROM
> (SELECT T1.itemid, T1.itemtype,
> MIN(T2.itemid) AS x_itemid
> FROM tbl AS T1
> LEFT JOIN tbl AS T2
> ON T1.itemtype <> T2.itemtype
> AND T1.itemid < T2.itemid
> GROUP BY T1.itemid, T1.itemtype) AS T
> GROUP BY itemtype, x_itemid
> If performance is an issue then you could do this for smaller subsets
> of rows and then combine the results.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Uri,
Replace
insert into #test values (8,'d')
with
insert into #test values (8,'a')
and see the difference
With warm regards
Jatinder Singh|||So David's script gave me a wrong output.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123050090.479481.14310@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Replace
> insert into #test values (8,'d')
> with
> insert into #test values (8,'a')
> and see the difference
> With warm regards
> Jatinder Singh
>|||Hi Uri,
It gave correct ouput to me.
Start End ItemType
8 9 A -- (1)
1 3 A -- (2)
4 5 B
6 7 C
The only thing is (1) appears at top which can be easily adjusted by
using ored by clause
With warm regards
Jatinder Singh|||Hi
Should not be 1 for MIN and 9 for MAX for A?
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123056485.365685.300130@.g14g2000cwa.googlegroups.com...
> Hi Uri,
> It gave correct ouput to me.
> Start End ItemType
> 8 9 A -- (1)
> 1 3 A -- (2)
> 4 5 B
> 6 7 C
> The only thing is (1) appears at top which can be easily adjusted by
> using ored by clause
> With warm regards
> Jatinder Singh
>|||Hi Uri,
Again Let us see this
ItemID ItemType
1 A -- *
2 A -- * One Group with ItemType='a' Here
min(itemid)= 1 and max is 3
3 A -- * 1 3 A (One Row of Reuired Result)
4 B -- ^ Another Group with ItemType='b' Here
min(itemid)= 4 and max is 5
5 B -- ^ 4 5 B (Another Row of Reuired Result)
6 C -- ~Another Group with ItemType='c' Here
min(itemid)= 6 and max is 7 7 C -- 6 7 C
(Another Row of Reuired Result)
8 A -- Again 'A' is repeated but there is gap so it
is to be considerd as a
9 A -- Fresh Group
-- 8 9 A
So the resultant output produced by David's Query is Correct
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
I hope it made the author's requirements more clear.
With warm regards
Jatinder Singh|||The difference is that your query only gives one row per ItemType
rather than one row per contiguous sequence on ItemType. I call my
query a "condensed" or "stepped" sequence rather than an aggregation.
The point is that it shows the regions or periods over which a
particuar ItemType applies. In my interpretation that's what BTJ was
asking for.
David Portas
SQL Server MVP
--|||David
Thanks, I got it
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123058956.842173.89690@.o13g2000cwo.googlegroups.com...
> The difference is that your query only gives one row per ItemType
> rather than one row per contiguous sequence on ItemType. I call my
> query a "condensed" or "stepped" sequence rather than an aggregation.
> The point is that it shows the regions or periods over which a
> particuar ItemType applies. In my interpretation that's what BTJ was
> asking for.
> --
> David Portas
> SQL Server MVP
> --
>

Creating a Crosstab in SQL

Hello,
I have data in a table as follows:
number type percent
147823 MTOR 100
147823 CLOR 100
147964 CLOR 100
148078 MTOR 50
148126 CLOR 100
148126 MTOR 100
How do I write a sql to get it as follows:
number CLOR MTOR
147823 100 100
147964 100
148078 50
148126 100 100
Thanks
deodevIs this SQL 2005?|||Here are 2 ways...
--data
declare @.t table (number int, type char(4), [percent] int)
insert @.t
select 147823, 'MTOR', 100
union all select 147823, 'CLOR', 100
union all select 147964, 'CLOR', 100
union all select 148078, 'MTOR', 50
union all select 148126, 'CLOR', 100
union all select 148126, 'MTOR', 100
--calculation (one way)
select
number,
max(case when type = 'CLOR' then [percent] else null end) as CLOR,
max(case when type = 'MTOR' then [percent] else null end) as MTOR
from @.t
group by number
order by number
--calculation (another way)
select isnull(a.number, b.number) as number, a.[percent] as CLOR,
b.[percent] as MTOR
from
(select number, [percent] from @.t where type = 'CLOR') a full outer join
(select number, [percent] from @.t where type = 'MTOR') b on a.number =
b.number
order by number
"deodev" wrote:

> Hello,
> I have data in a table as follows:
> number type percent
> 147823 MTOR 100
> 147823 CLOR 100
> 147964 CLOR 100
> 148078 MTOR 50
> 148126 CLOR 100
> 148126 MTOR 100
> How do I write a sql to get it as follows:
> number CLOR MTOR
> 147823 100 100
> 147964 100
> 148078 50
> 148126 100 100
> Thanks
>
> --
> deodev|||Pain in the kiester :(|||Thanks ryan for the help :P This will work as well. Thanks to your
leet sql ninjaness :P
CREATE TABLE #TABLE ( NUMBER INT, COLUMN_NAME NVARCHAR(4), [PERCENT]
INT)
INSERT INTO #TABLE
VALUES(147823,'MTOR',100)
INSERT INTO #TABLE
VALUES(147823,'CLOR',100)
INSERT INTO #TABLE
VALUES(147964,'CLOR',100)
INSERT INTO #TABLE
VALUES(148078,'MTOR',50)
INSERT INTO #TABLE
VALUES(148126,'CLOR',100)
INSERT INTO #TABLE
VALUES(148126,'MTOR',100 )
DECLARE @.QUERY NVARCHAR(MAX)
SET @.QUERY=''
SET @.QUERY='SELECT NUMBER'
DECLARE @.COLUMNS TABLE ( COLUMN_NAME NVARCHAR(4) )
DECLARE CURSOR_COLUMNS CURSOR READ_ONLY
FOR SELECT DISTINCT COLUMN_NAME FROM #TABLE ORDER BY COLUMN_NAME
DECLARE @.COLUMN_NAME nvarchar(4)
OPEN CURSOR_COLUMNS
FETCH NEXT FROM CURSOR_COLUMNS INTO @.COLUMN_NAME
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
SET @.QUERY = @.QUERY + ',MAX(CASE WHEN COLUMN_NAME = ''' +
@.COLUMN_NAME + ''' THEN [PERCENT] ELSE NULL END) AS ' + @.COLUMN_NAME
END
FETCH NEXT FROM CURSOR_COLUMNS INTO @.COLUMN_NAME
END
SET @.QUERY = @.QUERY + ' FROM #TABLE GROUP BY NUMBER ORDER BY NUMBER'
CLOSE CURSOR_COLUMNS
DEALLOCATE CURSOR_COLUMNS
PRINT @.QUERY
EXEC sp_executesql @.QUERY
DROP TABLE #TABLE|||This seems OK for a small data set -
how would I code if I have about 500 number
--
PS. It is SQL 2005
deodev
"Ryan Randall" wrote:
> Here are 2 ways...
> --data
> declare @.t table (number int, type char(4), [percent] int)
> insert @.t
> select 147823, 'MTOR', 100
> union all select 147823, 'CLOR', 100
> union all select 147964, 'CLOR', 100
> union all select 148078, 'MTOR', 50
> union all select 148126, 'CLOR', 100
> union all select 148126, 'MTOR', 100
> --calculation (one way)
> select
> number,
> max(case when type = 'CLOR' then [percent] else null end) as CLOR,
> max(case when type = 'MTOR' then [percent] else null end) as MTOR
> from @.t
> group by number
> order by number
> --calculation (another way)
> select isnull(a.number, b.number) as number, a.[percent] as CLOR,
> b.[percent] as MTOR
> from
> (select number, [percent] from @.t where type = 'CLOR') a full outer join
> (select number, [percent] from @.t where type = 'MTOR') b on a.number =
> b.number
> order by number
> "deodev" wrote:
>|||Should work just fine.. now if you had 150 different COLUMN_NAME's in
your table (ie in my example) that might be a problem. since the select
statement pretty much builds a long string'd dynamic query using
ryans's way of creating the columns. hmm whats the maximum length of a
nvarchar(max) ? I've seem to have forgotten. :P|||The code from Ryan works -
I tried the code from P - have some syntax problems.
Thanks
a lot - greatly appreciated
Deo.
deodev
"jebuskrust@.gmail.com" wrote:

> Should work just fine.. now if you had 150 different COLUMN_NAME's in
> your table (ie in my example) that might be a problem. since the select
> statement pretty much builds a long string'd dynamic query using
> ryans's way of creating the columns. hmm whats the maximum length of a
> nvarchar(max) ? I've seem to have forgotten. :P
>|||That why we created Rac:)
www.rac4sql.net
<jebuskrust@.gmail.com> wrote in message
news:1145553907.545097.230810@.u72g2000cwu.googlegroups.com...
> Pain in the kiester :(
>|||You may find the PIVOT keyword helpful.
http://msdn2.microsoft.com/en-us/library/ms177410(SQL.90).aspx

creating a boolean measure

Hello,

I've created a new measure where my source column is of type Boolean and have selected aggregate type "None" (simply because I just need to show true or false while browsing my cube).

However, when i deploy my cube and browse it, it shows empty cells against my dimension.

It doesnt allow me to select aggregate "First Non Empty Value" and i get a messag saying "Semi-additive measure requires a time dimension"

any suggestions would be appreciated...This is my desired output

Accepted

Submission 1 True

Submission 2 False

Submission 3 True

.

.

.

and so on. where Submission is my dimension and Accepted is my Measure.

TIA

russzee

Any reason why you couldn't create, rather than a measure, a dimension like "AcceptStatus", with members "True" and "False"?|||

Deepak,

Thanks for your reply.. Thats a very valid question. I forgot to eloborate that I already have a dimension called "Accepted" and it works just fine while browsing the cube against "Submission".

However , my client application is Performance Point Dashboard Designer so I need to convert this to a Measure to be able to use it as a KPI in the client application.

Thanks

russzee

|||In that case, you could create a calculated measure, which returns a value based on the member of "Accepted" with data, rather than creating a cube measure. If you provide more details of your data and of the required KPI, that would help.

Create XML file with SS Management Studio Express

Hi Folks,

I have this problem using SQL Server Management Studio Express.
When browse query/table with XML type column I cannot create manualy XML field.
Is that possible using built-in XML editor ?

I make following steps:
1. Use AdventureWorks example Database
2. Create New Query on Person.Contact table

SELECT ContactID, Title, FirstName, AdditionalContactInfo
FROM Person.Contact

3. Execute query
4. Then : first 10 records contain XML data in AdditionalContactInfo field
5. I cannot insert manualy new XML data into record 11,12 e.t.c

I thing it is possible by built-in XML editor, but HOW ?

Is it possible to change such field using MS Access version 2002 ?

Thanks in advance !

Hi Aleko_b,

You can't directly edit the XML with SQL Server Management Studio or the Express version either. We are considering this functionality in a future release. You need to construct an INSERT statement within the T-SQL Editor and copy/paste the XML from the XML editor.

As far as Access 2003 is concerned, you will want to create a Linked Table via ODBC to the table in question. The XML field will look like a Memo field that you can edit for changing the contents of the XML field. Just be careful not to modify the tags. ;-)

Thank you,
Bill Ramos, Lead PM, SQL Server Manageabilty

|||

I just discovered the "XML Editor" today. Is there an instruction guide or a list of supported features available?

TIA,

barkingdog

Create XML file with SS Management Studio Express

Hi Folks,

I have this problem using SQL Server Management Studio Express.
When browse query/table with XML type column I cannot create manualy XML field.
Is that possible using built-in XML editor ?

I make following steps:
1. Use AdventureWorks example Database
2. Create New Query on Person.Contact table

SELECT ContactID, Title, FirstName, AdditionalContactInfo
FROM Person.Contact

3. Execute query
4. Then : first 10 records contain XML data in AdditionalContactInfo field
5. I cannot insert manualy new XML data into record 11,12 e.t.c

I thing it is possible by built-in XML editor, but HOW ?

Is it possible to change such field using MS Access version 2002 ?

Thanks in advance !

Hi Aleko_b,

You can't directly edit the XML with SQL Server Management Studio or the Express version either. We are considering this functionality in a future release. You need to construct an INSERT statement within the T-SQL Editor and copy/paste the XML from the XML editor.

As far as Access 2003 is concerned, you will want to create a Linked Table via ODBC to the table in question. The XML field will look like a Memo field that you can edit for changing the contents of the XML field. Just be careful not to modify the tags. ;-)

Thank you,
Bill Ramos, Lead PM, SQL Server Manageabilty

|||

I just discovered the "XML Editor" today. Is there an instruction guide or a list of supported features available?

TIA,

barkingdog

Create XML file with SS Management Studio Express

Hi Folks,

I have this problem using SQL Server Management Studio Express.
When browse query/table with XML type column I cannot create manualy XML field.
Is that possible using built-in XML editor ?

I make following steps:
1. Use AdventureWorks example Database
2. Create New Query on Person.Contact table

SELECT ContactID, Title, FirstName, AdditionalContactInfo
FROM Person.Contact

3. Execute query
4. Then : first 10 records contain XML data in AdditionalContactInfo field
5. I cannot insert manualy new XML data into record 11,12 e.t.c

I thing it is possible by built-in XML editor, but HOW ?

Is it possible to change such field using MS Access version 2002 ?

Thanks in advance !

Hi Aleko_b,

You can't directly edit the XML with SQL Server Management Studio or the Express version either. We are considering this functionality in a future release. You need to construct an INSERT statement within the T-SQL Editor and copy/paste the XML from the XML editor.

As far as Access 2003 is concerned, you will want to create a Linked Table via ODBC to the table in question. The XML field will look like a Memo field that you can edit for changing the contents of the XML field. Just be careful not to modify the tags. ;-)

Thank you,
Bill Ramos, Lead PM, SQL Server Manageabilty

|||

I just discovered the "XML Editor" today. Is there an instruction guide or a list of supported features available?

TIA,

barkingdog