October 29, 2009 at 7:41 am
I'm looking for some directional assistance here, not code, I'd like to figure that part out on my own.
Having looked at Jack's example here (http://www.sqlservercentral.com/Forums/Topic679052-148-1.aspx) I see that I can use a CTE and PIVOT to take a delimited list and separate into columns in the result set.
I have data where I'm tracking how many languages a provider speaks, so one record per provider per language. I can build a delimited list of all the languages per provider, but do I need to do this?
Can I just pivot the language values over the provider to create separate columns if I know the max number of languages already?
Or do I need to add a front-end to Jack's solution, building the delimited list of language values per provider?
I will work on sample data in the meantime.
Thanks,
Jon <-- that's for Jack's benefit 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 29, 2009 at 7:51 am
If you already have a list of all the languages, a simple crosstab (or pivot, whichever is easier for you) should solve it unless I'm misreading something here. Jeff wrote an article on them; link in my sig.
October 29, 2009 at 9:21 am
Thanks Seth, I tried to go down that road, but all the examples are using aggregation of some sort, which I wouldn't want to do, just pivot the straight data. I'll play with it some more and post if I have questions.
Thanks,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 29, 2009 at 11:46 am
jcrawf02 (10/29/2009)
Thanks Seth, I tried to go down that road, but all the examples are using aggregation of some sort, which I wouldn't want to do, just pivot the straight data. I'll play with it some more and post if I have questions.Thanks,
Jon
The aggregation is required to limit the number of result sets (eliminating remaining NULL values. (When pivoting varchar values usually MIN() or MAX() is used).
For example (sample from the post you referred to):
Min(CASE
WHEN row_id = 1 THEN split_value
ELSE NULL
END) AS col1,
This will result in a column [col1] containing split_value if row_id is 1, otherwise NULL.
If you'd have two elements to split (let's say 'a,b'), without the aggregation it would look something like
Col1 Col2
a NULL
NULL b
Using the aggregation it would result in
Col1 Col2
a b
The aggregation eliminated the NULL values from the ELSE part of the CASE statement.
I don't know any other way to explain the need for the aggregation... Hope it helps...
October 29, 2009 at 11:52 am
Thanks Lutz, that helps to explain what it's doing, and how I can use it better. I realized that it was required, but wasn't wrapping my head around how that applied to my situation.
Appreciate the help!
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 30, 2009 at 10:49 am
Ok, I don't think this will work for me, because I want to pivot data without knowing what the values are.
For example, if I'm working with provider languages, I know that English will be included, but if I want to know what the other languages are, I'd have to create a dynamic column or something to get this to happen, yes? Same for a cross-tab, since you have to know what you're looking for.
For example, the below has ten different languages, and won't know what they are up front?
IF object_id('Tempdb..#temp') IS NOT NULL
BEGIN DROP TABLE #temp END
CREATE TABLE #temp
(iRow int identity(1,1), -- identity column for primary key
data varchar(32),
description char(255))
--===== Add a Primary Key to maximize performance
IF OBJECT_ID('Tempdb..#temp') IS NULL
BEGIN
ALTER TABLE #temp
ADD CONSTRAINT PK_#temp_iRow
PRIMARY KEY CLUSTERED (iRow)
WITH FILLFACTOR = 100
END
-- Insert test data into table
-------------------------------------------------------
INSERT INTO #temp (data, description) VALUES ('A D','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('A K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('A B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('A L','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('A LAVAR H','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AAMIR K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AAMIR M','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AAMIR M','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('AARON A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON M','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON H','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON F','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON G','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON S','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON O','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON P','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON W','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON F','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON W','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON H','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON R','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON C','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON W','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON D','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON E','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON H','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON S','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON P','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON M','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON D','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON G','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON G','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AARON F','HINDI ')
INSERT INTO #temp (data, description) VALUES ('AARON B','SOMALI ')
INSERT INTO #temp (data, description) VALUES ('AARON B','SPANISH ')
INSERT INTO #temp (data, description) VALUES ('AASIM S','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('AASIM S','HINDI ')
INSERT INTO #temp (data, description) VALUES ('AASIM S','URDU ')
INSERT INTO #temp (data, description) VALUES ('ABBAS A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABBAS S','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABBAS A','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABBAS A','FRENCH ')
INSERT INTO #temp (data, description) VALUES ('ABBAS A','GERMAN ')
INSERT INTO #temp (data, description) VALUES ('ABBE C','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABBOT S','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABBY L','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDAL A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDEL H','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDEL E','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDELHAMED A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDELHAMED A','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABDELHAMID B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDI G','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDI G','PERSIAN/FARSI ')
INSERT INTO #temp (data, description) VALUES ('ABDOLALI E','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDOLREZA A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDU H','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDU H','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABDU H','SPANISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL B','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL W','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL W','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL S','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL Y','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL R','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUL B','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABDUL A','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABDUL Z','HINDI ')
INSERT INTO #temp (data, description) VALUES ('ABDUL W','HINDI ')
INSERT INTO #temp (data, description) VALUES ('ABDUL A','HINDI ')
INSERT INTO #temp (data, description) VALUES ('ABDUL A','PUNJABI ')
INSERT INTO #temp (data, description) VALUES ('ABDUL Z','URDU ')
INSERT INTO #temp (data, description) VALUES ('ABDUL A','URDU ')
INSERT INTO #temp (data, description) VALUES ('ABDUL W','URDU ')
INSERT INTO #temp (data, description) VALUES ('ABDULLA A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDULLA A','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABDULLA A','FRENCH ')
INSERT INTO #temp (data, description) VALUES ('ABDULLAH K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDULLAH K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDULLAH O','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDULLAH K','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABDULMAWLA A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUR K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUR R','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABDUS M','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABED K','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABEER A','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABEER A','ARABIC ')
INSERT INTO #temp (data, description) VALUES ('ABHA G','ENGLISH ')
INSERT INTO #temp (data, description) VALUES ('ABHA G','HINDI ')
SELECT * FROM #temp
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 30, 2009 at 11:07 am
What would your expected result set in general and especially in terms of handling dups (e.g. for 'AARON K' and 'ENGLISH'?
The issue itself sounds like a task for dynamic cross tab (as described in the last link in my signature) together with ROW_NUMBER function.
But I'd need more information regarding the expected result set...
October 30, 2009 at 11:50 am
Dupes aren't probably dupes due to the full value in that column, so for this purpose you can ignore de-duping. Resultset I'm looking for would look something like the attached, each separate language description in it's own column.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 30, 2009 at 1:00 pm
Here's an example on how to convert rows into columns.
-- remove intermediate table if existing
IF object_id('Tempdb..#intermed') IS NOT NULL
BEGIN DROP TABLE #intermed END
-- declare variable to hold dynamic SQL
DECLARE @sql nvarchar(900)
-- part 1: fixed statement
SET @sql = N'SELECT data'
-- build intermediate table with description numbered
;WITH CTE_test
AS
(
SELECT data,
description,
row_number() OVER(PARTITION BY data ORDER BY min(irow)) AS row
FROM #temp
GROUP BY data, description
)
SELECT * INTO #intermed FROM CTE_test
-- part 2: dynamic statement
SELECT @sql = @sql + N',
MAX(CASE WHEN row='+cast(a.row as varchar(5))+'
THEN description ELSE '''' END) AS [Col'+cast(a.row as varchar(2))+']'
FROM #intermed a
GROUP BY a.row
ORDER BY a.row
-- part 2: fixed statement
SET @sql = @sql + N'
FROM #intermed
GROUP BY data
ORDER BY data'
PRINT @sql -- for debugging
--EXEC sp_executesql @sql
October 30, 2009 at 1:19 pm
Thank you! I really appreciate your help on this, the process is more important than the example for me here.
Now I need to go read Jeff's articles again.
Thanks again!
Jon:-D
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply