June 27, 2012 at 11:30 am
I have two fields in a SQL Server 2008 view.
ID and SubID. There are up to 24 subIDs for any ID.
In other words, there can be up to 24 rows for an ID field.
I want to present these across as below:
ID SubID-1 SubID-2...and so on with null in extra subID columns.
Is there a way to do this using a SQL query?
I am a newbie, so any help would be greatly appreciated. Thanks much.
June 27, 2012 at 12:10 pm
smitausa (6/27/2012)
I have two fields in a SQL Server 2008 view.ID and SubID. There are up to 24 subIDs for any ID.
In other words, there can be up to 24 rows for an ID field.
I want to present these across as below:
ID SubID-1 SubID-2...and so on with null in extra subID columns.
Is there a way to do this using a SQL query?
I am a newbie, so any help would be greatly appreciated. Thanks much.
There is a method, in fact more than one method to do so. But to assist you, you should assist us.
Please post your table definition, some sample data and a what you desire using the sample data.
Now to do the above please click on the first link in my signature block. The article explains how to do what I have requested and includes sample T-SQL to allow you to do it, rather quickly and simply.
June 27, 2012 at 12:19 pm
The method chosen (adding to what Ron said) will partly depend on the information you can provide back.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 1:39 pm
The table is as below
[id] [subid] [pct]
abc w 10
abc e 20
abc g 70
def a 100
ghi x 20
ghi h 80
jkl p null
jkl l 1
jkl m null
The output should look like below.
abc w 10 e 20 g 70
def a 100
ghi x 20 h 80
jkl p null l 1 m null
Sorry, it is my post here. Thanks.
June 27, 2012 at 6:40 pm
tinausa (6/27/2012)
The table is as below[id] [subid] [pct]
abc w 10
abc e 20
abc g 70
def a 100
ghi x 20
ghi h 80
jkl p null
jkl l 1
jkl m null
The output should look like below.
abc w 10 e 20 g 70
def a 100
ghi x 20 h 80
jkl p null l 1 m null
Sorry, it is my post here. Thanks.
There is a solution to a similar problem here: http://www.sqlservercentral.com/Forums/Topic1319158-391-1.aspx#bm1319226
You can also search PIVOT and crosstab on the forum to find what you're looking for.
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
June 28, 2012 at 3:59 am
Thanks, I will check out the link.
I saw various solutions but most of them are not applicable in my case since the id and subid columns have unique values of 18 characters each. They do not repeat.
So the output has to have columns such as
id subid1 pct1 subid2 pct2 and so on.
Thanks.
June 28, 2012 at 6:43 am
tinausa (6/27/2012)
The table is as below[id] [subid] [pct]
abc w 10
abc e 20
abc g 70
def a 100
ghi x 20
ghi h 80
jkl p null
jkl l 1
jkl m null
The output should look like below.
abc w 10 e 20 g 70
def a 100
ghi x 20 h 80
jkl p null l 1 m null
Sorry, it is my post here. Thanks.
Please see the first link in my signature line below to get the best help the quickest.
For your output, are those supposed to be in columns or all concatenated into a single column?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2012 at 6:59 am
Thanks Jeff, I need them to be in columns - a table actually.
Tina
June 28, 2012 at 12:48 pm
tinausa (6/28/2012)
Thanks Jeff, I need them to be in columns - a table actually.Tina
Since there are an unknown number of potential columns, the following article tells you how to do it.
http://www.sqlservercentral.com/articles/Crosstab/65048/
If you'd like a coded answer, it would be helpful if the data you presented were in a readily consumable format. Please see the first link in my signature line below for how to do that properly.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2012 at 6:38 am
It can be done in a no. of ways depending on whether the no. of columns in the query would be static or Dynamic. Following are a few ways of doing it:
--Creating Tables
Create Table Ex
(id Char(3),
subid Char(1),
pct int )
--Inserting Sample Data
Insert Into Ex
Select 'abc', 'w', 10
Union ALL
Select 'abc', 'e', 20
Union ALL
Select 'abc', 'g', 70
Union ALL
Select 'def', 'a', 100
Union ALL
Select 'ghi', 'x', 20
Union ALL
Select 'ghi', 'h', 80
Union ALL
Select 'jkl', 'p', null
Union ALL
Select 'jkl', 'l', 1
Union ALL
Select 'jkl', 'm', null
--Query With Case
Select id, Max(subid1) As subid1, Max(pct1) As pct1, Max(subid1) As subid1, Max(pct2) As pct2, Max(subid3) As subid3, Max(pct3) As pct3 From
(Select id,
(Case When rn = 1 Then subid Else '' End) As subid1,
(Case When rn = 1 Then subid Else '' End) As pct1,
(Case When rn = 2 Then subid Else '' End) As subid2,
(Case When rn = 2 Then subid Else '' End) As pct2,
(Case When rn = 3 Then subid Else '' End) As subid3,
(Case When rn = 3 Then subid Else '' End) As pct3
From
(Select *, Row_number() Over (Partition By id Order By id) As rn From Ex) As a ) As b
Group By id
--Static Pivot
Select id, IsNULL(Max(subid1), '') As subid1, IsNULL(Max(subid2), '') As subid2, IsNULL(Max(subid3), '') As subid3 From
(Select id, [subid1], [subid2], [subid3] From
(Select *, 'subid' + Cast(Row_Number() Over (Partition By id Order By id) As Varchar(10) ) As rn From Ex) As a
Pivot
(Max(subid) For rn IN ([subid1], [subid2], [subid3]) ) As pvt ) As b
Group By id
--Dynamic Pivot
Declare @cols Varchar(max), @cols1 Varchar(max), @sql Varchar(max)
Declare @temp Table(Cols Varchar(10) )
Insert Into @temp
Select Distinct 'SubId' + Cast(Row_number() Over (Partition By id Order By id) As Varchar(10) ) As rn From Ex
Select @cols = Coalesce(@cols + ', ', '') + QuoteName(Cols) From @temp
Select @cols1 = Coalesce(@cols1 + '), ''''), IsNULL(Max(', '') + QuoteName(Cols) From @temp
Set @cols1 = 'IsNULL(Max(' + @cols1 + '), '''')'
Set @sql = 'Select id, '+@cols1+' From
(Select id, '+@cols+' From
(Select *, ''subid'' + Cast(Row_Number() Over (Partition By id Order By id) As Varchar(10) ) As rn From Ex) As a
Pivot
(Max(subid) For rn IN ('+@cols+') ) As pvt ) As b
Group By id'
Execute (@sql)
If the Columns would remain static then you can use any one of the Case Query or the Static Pivot.
If the no. of columns being used by the query is bound to change then you can use the Dynamic Pivot.
Hope this helps.
June 29, 2012 at 9:47 am
This is a very interesting quandary indeed. I am sure this can be done using a little less code but here is what I came up with:
SET NOCOUNT ON
GO
/***********************************************************************
1) Create the sample data
************************************************************************/
DECLARE @srcData TABLE
(
[id]varchar(4),
[subid]varchar(2),
[pct]tinyint
);
INSERT INTO @srcData
VALUES('abc','w', 10),
('abc','e', 20),
('abc','g', 70),
('def','a', 100),
('ghi','x', 20),
('ghi','h', 80),
('jkl','p', null),
('jkl','l', 1),
('jkl','m', null);
/***********************************************************************
2) Declare varibles & create temp tables for my iteration routine below
Create temp tables:
@Step1:To get a distinct list & count of ID's
@Step2:Copy of the source data with (subid & pct concatinated)
@out:temp table used for storing output
***********************************************************************/
DECLARE @i int = 1, @i2 int, @ii int, @id varchar(4),
@rows int, @results varchar(10), @stuff varchar(100) = ''
DECLARE @step1 TABLE
(
intIDENTITY(1,1),
[id]varchar(4),
[rows]int
);
DECLARE @step2 TABLE
(
intIDENTITY(1,1),
[id]varchar(4),
[results]varchar(100)
);
DECLARE @out TABLE
(
[results]varchar(100)
);
/***********************************************************************
3) Populate @step1 & @step2
***********************************************************************/
INSERT INTO @step1 ([id],[rows])
SELECTu.[id],
u.[rows]
FROM
(
SELECTu.[id],
COUNT(u.[id]) AS [rows]
FROM
(
SELECT DISTINCT [id]
FROM @srcData
) u
JOIN @srcData f ON u.id = f.id
GROUP BY u.id
) u;
SELECT * FROM @step1;
INSERT INTO @step2 ([id],[results])
SELECT[id],
subid + ' ' + ISNULL(CAST(pct AS varchar(5)),'null') [x]
FROM @srcData;
SELECT '@step2' AS
SELECT * FROM @step2;
/***********************************************************************
4) Iterate thorugh step1 & @step2 to put the final results into @out
***********************************************************************/
SET @ii = (SELECT COUNT(*) FROM @step1);
--For each DISTINCT (unique) ID
WHILE @i <= @ii
BEGIN
SELECT @id =(SELECT [id] FROM @step1 WHERE = @i),
@rows = (SELECT [rows] FROM @step1 WHERE = @i),
@i2 = 1;
SET @stuff = CAST(@id AS varchar(5))
-- collect and concatinate the results
WHILE @i2 <= @rows
BEGIN
SET @results = (SELECT TOP 1 results FROM @step2 WHERE id = @id);
SET @stuff = @stuff + ' ' + @results;
DELETE FROM @step2
WHERE id = @id
AND results = (SELECT TOP 1 results FROM @step2 WHERE id = @id);
SET @i2 = @i2+1;
END
INSERT INTO @out
SELECT @stuff
SET @stuff = ''
SET @i = @i+1
END;
/***********************************************************************
5) Output
***********************************************************************/
SELECT * FROM @out;
GO
Here's what is going on:
1) We create the sample data
2) Declare varibles & create temp tables for my iteration routine below
What the temp tables do:
@Step1:To get a distinct list & count of ID's
@Step2:Copy of the source data with (subid & pct concatinated)
@out:temp table used for storing output
3) Populate @step1 and @step2
4) Iterate thorugh step1 & @step2 to put the final results into @out
Hope this helps!
-- Itzik Ben-Gan 2001
July 6, 2018 at 4:26 am
Hi everyone i have a very request regarding displaying rows in horizontal i want the columns name of a SQL server table displayed horizontally like following My query :
NB AFPO is the name of the table i want to display columns name from
Also, i have a lot of tables to get the columns name displayed in Horizontal so help would be appreciated
My query:
select column_id, name from sys.all_columns where object_id = OBJECT_ID(N'AFPO') order by column_id
displays :
column_id name
1 POSNR
2 MANDT
3 AUFNR
4 KDAUF
And i want this to be displayed this way :
column_id 1 2 3 4
name POSNR MANDT AUFNR KDAUF
and no way to find out how to achieve that. Could you help me?
Steph
July 6, 2018 at 4:49 am
Heslous - Friday, July 6, 2018 4:26 AMHi everyone i have a very request regarding displaying rows in horizontal i want the columns name of a SQL server table displayed horizontally like following My query :
NB AFPO is the name of the table i want to display columns name from
Also, i have a lot of tables to get the columns name displayed in Horizontal so help would be appreciatedMy query:
select column_id, name from sys.all_columns where object_id = OBJECT_ID(N'AFPO') order by column_iddisplays :
column_id name
1 POSNR
2 MANDT
3 AUFNR
4 KDAUFAnd i want this to be displayed this way :
column_id 1 2 3 4
name POSNR MANDT AUFNR KDAUFand no way to find out how to achieve that. Could you help me?
6 year old topic 😉
As for how to do this, have you had a look at the article that Jeff linked above? It should greatly help you get what you're after. Have a read and try yourself; if you get stuck please do post what you've tried so we can help you further.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 6, 2018 at 6:17 am
Thom A - Friday, July 6, 2018 4:49 AMHeslous - Friday, July 6, 2018 4:26 AMHi everyone i have a very request regarding displaying rows in horizontal i want the columns name of a SQL server table displayed horizontally like following My query :
NB AFPO is the name of the table i want to display columns name from
Also, i have a lot of tables to get the columns name displayed in Horizontal so help would be appreciatedMy query:
select column_id, name from sys.all_columns where object_id = OBJECT_ID(N'AFPO') order by column_iddisplays :
column_id name
1 POSNR
2 MANDT
3 AUFNR
4 KDAUFAnd i want this to be displayed this way :
column_id 1 2 3 4
name POSNR MANDT AUFNR KDAUFand no way to find out how to achieve that. Could you help me?
6 year old topic 😉
As for how to do this, have you had a look at the article that Jeff linked above? It should greatly help you get what you're after. Have a read and try yourself; if you get stuck please do post what you've tried so we can help you further.
Hi Thom
i do not see the Jeff's link you talk about
Steph
July 6, 2018 at 6:25 am
create table #temp (id varchar(50),subid varchar(10),pcs varchar(10))
insert into #temp select 'abc', 'w 10',NULL
insert into #temp select 'abc', 'e 20',NULL
insert into #temp select 'abc', 'g 70',NULL
insert into #temp select 'def', 'a 100',Null
insert into #temp select 'ghi', 'x 20',Null
insert into #temp select 'ghi', 'h 80',Null
insert into #temp select 'jkl', 'p',Null
insert into #temp select 'jkl', 'l','1'
insert into #temp select 'jkl', 'm', null
Select id,stuff((select ''+subid,isnull(pcs,'')
from #temp t1
where t1.id=t2.id
for xml path ('')),1,1,'')
from #temp t2
group by id
***The first step is always the hardest *******
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply