August 16, 2013 at 9:22 am
have a request to create report of values from lookup tables, these are not related so can't join the various tables. I started by creating a temp table with a field for each of the lookup table values then inserted one column at a time which gave me all the data i needed in one table, but when output data i get results like
col1 col2 col3
a null null
b null null
c null null
d null null
e null null
f null null
g null null
null 1 null
null 2 null
null 3 null
null 4 null
null null yes
null null no
null null maybe
the customer would like to see the values for the columns all start in row 1
if there a way to do this any help would be greatly appreciated - Scott
August 16, 2013 at 9:37 am
scott.kitson (8/16/2013)
have a request to create report of values from lookup tables, these are not related so can't join the various tables. I started by creating a temp table with a field for each of the lookup table values then inserted one column at a time which gave me all the data i needed in one table, but when output data i get results likecol1 col2 col3
a null null
b null null
c null null
d null null
e null null
f null null
g null null
null 1 null
null 2 null
null 3 null
null 4 null
null null yes
null null no
null null maybe
the customer would like to see the values for the columns all start in row 1
if there a way to do this any help would be greatly appreciated - Scott
I am not sure I understand your request here. Are you wanting only rows with values from each of three columns? What would you put in a row where there are not 3 values?
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
August 16, 2013 at 9:53 am
thanks for replying Sean
create table #temp_test
(
PROGRAM varchar(30),
MARKET_SEGMENT varchar(50),
RESP_ORG varchar(12),
APPROVAL varchar(50)
)
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('ABC')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('DEF')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('GHI')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('JKL')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('MNO')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('PQR')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('STU')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('UVW')
INSERT INTO #TEMP_TEST
(PROGRAM)
values ('XYZ')
INSERT INTO #TEMP_TEST
(MARKET_SEGMENT)
VALUES ('1')
INSERT INTO #TEMP_TEST
(MARKET_SEGMENT)
VALUES ('2')
INSERT INTO #TEMP_TEST
(MARKET_SEGMENT)
VALUES ('3')
INSERT INTO #TEMP_TEST
(MARKET_SEGMENT)
VALUES ('4')
INSERT INTO #TEMP_TEST
(MARKET_SEGMENT)
VALUES ('5')
INSERT INTO #TEMP_TEST
(APPROVAL)
VALUES ('YES')
INSERT INTO #TEMP_TEST
(APPROVAL)
VALUES ('NO')
INSERT INTO #TEMP_TEST
(APPROVAL)
VALUES ('MAYBE')
want results to be
Program Market_segment Approval
ABC 1 yes
CDE 2 no
FGH 3 maybe
IJK 4 null
LMN 5 null
MNO null null
PQR null null
STU null null
VWY null null
XYZ null null
Hopefully this makes it clearer what i am trying to do
August 16, 2013 at 10:10 am
What a completely bizarre requirement. You are literally mixing values between rows. This will work for the sample data.
select program, market_segment, Approval
from
(
select program, ROW_NUMBER() over(order by PROGRAM) as RowNum
from #temp_test
where PROGRAM is not null
) p
left join
(
select MARKET_SEGMENT, ROW_NUMBER() over(order by MARKET_SEGMENT) as RowNum
from #temp_test
where MARKET_SEGMENT is not null
) m on m.RowNum = p.RowNum
left join
(
select APPROVAL, ROW_NUMBER() over(order by APPROVAL) as RowNum
from #temp_test
where APPROVAL is not null
) a on a.RowNum = p.RowNum
Very important note, this will only work when Program has more rows than the other two tables.
What could you possibly be doing with a strange bit of output? You now have data in 3 columns are complete unrelated to each other even though they all came from the same table.
_______________________________________________________________
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/
August 16, 2013 at 10:14 am
Thanks Sean - will try it out ASAP
the customer wants to see the values of "lookup tables" (values in drop-down select boxes) in the application in one place - a report - rather than going to the various screens in the application
August 16, 2013 at 10:15 am
This is a weird requirement, I believe that this is a resultset and not a table as the sample data you gave us. This query might give you an idea of what you could do.
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER(ORDER BY ISNULL( PROGRAM, 'ZZZZZZZZZZZZZ')) rn1,
ROW_NUMBER() OVER(ORDER BY ISNULL( MARKET_SEGMENT, 'ZZZZZZZZZZZZZ')) rn2,
ROW_NUMBER() OVER(ORDER BY ISNULL( APPROVAL, 'ZZZZZZZZZZZZZ')) rn3
FROM #temp_test
)
SELECT a.PROGRAM, b.MARKET_SEGMENT, c.APPROVAL
FROM CTE a
JOIN CTE b ON a.rn1 = b.rn2
JOIN CTE c ON a.rn1 = c.rn3
August 16, 2013 at 10:17 am
scott.kitson (8/16/2013)
Thanks Sean - will try it out ASAPthe customer wants to see the values of "lookup tables" (values in drop-down select boxes) in the application in one place - a report - rather than going to the various screens in the application
OK that makes sense. Make it easier on yourself and create a section for each of your lookup tables instead of trying to cram them all into a single result set. It would be more clear to the user, make more sense from a coding perspective, and perform better because you don't have to do all these weird gyrations to force a square peg in the round hole.
_______________________________________________________________
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/
August 16, 2013 at 10:38 am
thanks Luis - will try that too
August 16, 2013 at 1:58 pm
i tried both Sean and Luis suggestions
On Luis's a got a timeout error on the query before i finished adding all the columns . There are 18 columns i am trying to cram into one table to generate a report from with a parameter of business segment
Sean's worked great with the first business segment i tried, then the next business segment i got the values in each column repeated many times
this is a Cold fusion application that i am running the SQL from. the user wants to see all his (business segment) for all 18 columns in an excel spreadsheet.
Any ideas on why it works on some business segments, but not on others
Thanks again for your help
August 16, 2013 at 2:03 pm
scott.kitson (8/16/2013)
i tried both Sean and Luis suggestionsOn Luis's a got a timeout error on the query before i finished adding all the columns . There are 18 columns i am trying to cram into one table to generate a report from with a parameter of business segment
Sean's worked great with the first business segment i tried, then the next business segment i got the values in each column repeated many times
this is a Cold fusion application that i am running the SQL from. the user wants to see all his (business segment) for all 18 columns in an excel spreadsheet.
Any ideas on why it works on some business segments, but not on others
Thanks again for your help
Short of seeing actual ddl and data the best we can do is guess. Maybe you need to add a distinct to your queries?
_______________________________________________________________
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/
August 18, 2013 at 11:41 pm
Here's another way you can try:
SELECT PROGRAM = MAX(CASE n WHEN 1 THEN [Type] END)
,MARKET_SEGMENT = MAX(CASE n WHEN 2 THEN [Type] END)
,APPROVAL = MAX(CASE n WHEN 3 THEN [Type] END)
FROM (
SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY n ORDER BY (SELECT NULL))
FROM #TEMP_TEST
CROSS APPLY (
VALUES (1,PROGRAM), (2,MARKET_SEGMENT), (3,APPROVAL)) a (n, [Type])
WHERE [Type] IS NOT NULL) a
GROUP BY rn;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 19, 2013 at 8:28 am
Thanks Dwain - will give it a try
August 19, 2013 at 11:05 am
Dwain tried your code and works great on the example, however when i try it on all columns the resulting data is spread all over will have some null rows then data then more nulls the data scattered over the columns
here is full temp data
create table #temp_lookup
(
PROGRAM varchar(30),
MARKET_SEGMENT varchar(50),
RESP_ORG varchar(12),
APPROVAL varchar(50),
USER_FIELD_BL2 varchar(50),
USER_FIELD_BL4 varchar(50),
USER_FIELD_BL6 varchar(50),
USER_FIELD_UD1 varchar(50),
USER_FIELD_UD2 varchar(50),
USER_FIELD_UD3 varchar(50),
USER_FIELD_UD4 varchar(50),
USER_FIELD_UD5 varchar(50),
BU_DESC varchar(25),
DEPT varchar(50),
DEP_PROJECT_ID varchar(15),
PROJECT_COLLECTOR varchar(50),
PHYSICAL_LOCATION varchar(50),
REQ_BUS_UNIT varchar(50)
)
then a bunch of inserts using select from a column in a table using distinct
here is Dwain's code expanded to handle all the columns
SELECT DEPT = MAX(CASE n WHEN 1 THEN [Type] END)
,MARKET_SEGMENT = MAX(CASE n WHEN 2 THEN [Type] END)
,APPROVAL = MAX(CASE n WHEN 3 THEN [Type] END)
,USER_FIELD_BL2 = MAX(CASE n WHEN 4 THEN [Type] END)
,USER_FIELD_BL4 = MAX(CASE n WHEN 5 THEN [Type] END)
,USER_FIELD_BL6 = MAX(CASE n WHEN 6 THEN [Type] END)
,USER_FIELD_UD1 = MAX(CASE n WHEN 7 THEN [Type] END)
,USER_FIELD_UD2 = MAX(CASE n WHEN 8 THEN [Type] END)
,USER_FIELD_UD3 = MAX(CASE n WHEN 9 THEN [Type] END)
,USER_FIELD_UD4 = MAX(CASE n WHEN 10 THEN [Type] END)
,USER_FIELD_UD5 = MAX(CASE n WHEN 11 THEN [Type] END)
,BU_DESC = MAX(CASE n WHEN 12 THEN [Type] END)
,PROGRAM = MAX(CASE n WHEN 13 THEN [Type] END)
,DEP_PROJECT_ID = MAX(CASE n WHEN 14 THEN [Type] END)
,PROJECT_COLLECTOR = MAX(CASE n WHEN 15 THEN [Type] END)
,PHYSICAL_LOCATION = MAX(CASE n WHEN 16 THEN [type] END)
,REQ_BUS_UNIT = MAX(CASE n WHEN 17 THEN [Type] END)
,RESP_ORG = MAX(CASE n WHEN 18 THEN [Type] END)
FROM (
SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY n ORDER BY (SELECT NULL))
FROM ##TEMP_LOOKUP
CROSS APPLY (
VALUES (1,DEPT), (2,MARKET_SEGMENT), (3,APPROVAL), (4,USER_FIELD_BL2), (5,USER_FIELD_BL4), (6,USER_FIELD_BL6), (7,USER_FIELD_UD1), (8,USER_FIELD_UD2), (9,USER_FIELD_UD3),
(10,USER_FIELD_UD4), (11,USER_FIELD_UD5), (12,BU_DESC), (13,PROGRAM), (14,DEP_PROJECT_ID), (15,PROJECT_COLLECTOR), (16,PHYSICAL_LOCATION), (17,REQ_BUS_UNIT), (18,RESP_ORG)
) a (n, [Type])
WHERE [Type] IS NOT NULL) a
GROUP BY rn;
August 19, 2013 at 5:30 pm
You might try changing:
ORDER BY (SELECT NULL)
To ORDER BY the column with the most entries.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 20, 2013 at 4:47 am
scott.kitson (8/19/2013)
CROSS APPLY (VALUES (1,DEPT), (2,MARKET_SEGMENT), (3,APPROVAL), (4,USER_FIELD_BL2), (5,USER_FIELD_BL4), (6,USER_FIELD_BL6), (7,USER_FIELD_UD1), (8,USER_FIELD_UD2), (9,USER_FIELD_UD3),
(10,USER_FIELD_UD4), (11,USER_FIELD_UD5), (12,BU_DESC), (13,PROGRAM), (14,DEP_PROJECT_ID), (15,PROJECT_COLLECTOR), (16,PHYSICAL_LOCATION), (17,REQ_BUS_UNIT), (18,RESP_ORG)
) a (n, [Type])
WHERE [Type] IS NOT NULL) a
GROUP BY rn;
I think all you have to do is to add ORDER BY rn at the end.
GROUP BY rn
ORDER BY rn;
Without an ORDER BY, the order of the returned rows is undefined.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply